You discover that the schema changes that were recently made to your SQL Server 2005 database have caused your Web site to stop functioning. It is unclear who made the changes. Domain.com now mandates that all changes to the database schema be tracked. You need to implement a mechanism that will track schema changes in your database.
What should you do?
A.
Implement a stored procedure that writes data about schema changes to a log table.
B.
Implement DDL AFTER triggers that write user and schema information to a log table.
C.
Implement a DML INSTEAD OF trigger that writes data about schema changes to a log table.
D.
Implement a DML AFTER trigger that writes data about schema changes to a log table.
Explanation:
A trigger is a specialized implementation of a Transact-SQL or CLR batch that automatically runs in response to an event within the database. You can create two types of triggers in SQL Serer 2005: data manipulation language (DML) triggers and data definition language (DDL) triggers. DDL triggers, which run in response to DDL events that occur on the server such as creating, altering, or dropping an object, are used for database administration tasks such as auditing and controlling object access.