70-444 and Triggers

When preparing for the 70-444 exam, you should have a solid understanding of triggers (especially DDL triggers).

As a reminder, DML triggers respond to Data Manipulation Language (DML) statements such as INSERT, UPDATE, and DELETE. DDL triggers respond to Data Definition Language (DDL) statements such as CREATE, ALTER, and DROP.

DDL triggers are new in SQL Server 2005 so you can expect Microsoft to include exam questions related to them whenever they are mentioned on the exam objectives. DDL triggers are directly mentioned on the 70-431 and 70-433 exam objectives and indirectly on the 70-444 exam objectives.

The 70-444 exam alludes to DDL triggers in the context of optimizing a database control strategy. Within organizations, procedures aren’t always followed so you may want to ensure that changes to database objects are recorded and key personnel are notified. This can be done by implementing an auditing strategy with DDL triggers.

For example, you may want to control the modification of any database objects (such as tables, views, stored procedures, and so on). By implementing an ALTER DDL trigger with a database scope, you can ensure that any ALTER statement executed against any database object in a specific database is captured.

Now, when someone alters a table within your database, you can execute a stored procedure that logs the event into an audit table and sends an email to a key group of database administrators.

You can get a lot deeper into the triggers, but when preparing for the MCITP: Database Administrator exams, you can go a long way just knowing the differences between DML triggers and DDL triggers.

For example, you should ace this question:
You are a database developer for the Hogwart’s University. Due to unauthorized changes to a database table in the Students database, several views have been broken. You are tasked with creating a method of identifying exactly when, and by who, any changes to database tables are made. What should you do?
A. Create stored procedure to make changes to database tables.
B. Remove all permissions to the database tables.
C. Create a DML trigger.
D. Create a DDL trigger.
*

For a more detailed understanding of triggers, check out Chapter 5 of the MCITP: SQL Server 2005 Database Administrator All-In-One book. This chapter also includes practice test questions for the 7-431, 70-443, and 70-444 exams related to triggers.

* The answer to the question is D. A DDL trigger could be created to capture all ALTER statements issued against the database. If a stored procedure was created, there’d be no guarantee it’d be used; in other words, someone could alter the table directly without using the stored procedure. A DML trigger could be used to capture data changes, but not object changes.