You work as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Your duties include administrating a SQL Server 2005 database named Certkiller -DB04. It has come to your attention that schema changes were made on Certkiller -DB04. Due to these changes on Certkiller -DB04 your Web site stopped functioning. It is however unclear who made the changes. You have received mandate from the CIO that all changes to the database schema be tracked. You implement a mechanism that will track schema changes in Certkiller -DB04.
What should you do?
A.
You need to create a DDL trigger to roll back any changes to the tables if the changes affect the columns in the view.
B.
You need to implement a DML AFTER trigger that writes data about schema changes to a log table.
C.
You need to implement a DML INSTEAD OF trigger that writes data about schema changes to a log table.
D.
You need to implement DDL AFTER triggers that write user and schema information to a log table.
E.
You need to implement a stored procedure 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 Certkiller -DB04: 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.