You are the administrator of a SQL Server 2005 computer named SQL1. SQL1 contains two databases named Production and DW. You create a new database named Staging.
The Staging database temporarily stores and manipulates data as the data moves from the Production database into the DW database. You need to ensure that tables are not removed from the Staging database. You also need to ensure that any attempts to remove tables are logged. Other databases must not be affected by your solution. What should you do?
A.
Create a DML trigger on each table to prevent deletion of data.
B.
Create an event notification to fire when the TRUNCATE TABLE Transact-SQL statement is submitted to the Staging database.
C.
Create a DDL trigger that uses the ON DATABASE parameter for the Staging database.
D.
Create a DDL trigger that uses the ON ALL SERVER parameter.
Explanation:
A DDL trigger created with the ON DATABASE parameter will fire whenever the T-SQL language event configured to fire the DDL trigger occurs anywhere in the DATABASE.