You work as the database administrator at Domain.com. All servers on the Domain.com network run Windows Server 2003 and all database servers run SQL Server 2005. The Domain.com network contains a database server named Certkiller -DB01 B01. Certkiller -DB01 hosts a database named CK_Sales that stores sales data for the company. The tables in the CK_Sales database are shown in the following database diagram.
Several products listed in the Products table have been discontinued. You want to delete the data from the Products table that refer to the discontinued products. You also want to copy data from the Invoices table that are related to the discontinued products to a separate table named DiscontinuedProductInvoices. You decide to use triggers to accomplish this task. The first trigger will delete rows from the Products table while the second trigger will to copy data from the Invoices table to the DiscontinuedProductInvoices table. You want the second trigger to execute once the first trigger is executed.
What should you do?
A.
Run the sp_configure ‘nested triggers’, 0 statement.
B.
Run the sp_configure ‘nested triggers’, 1 statement.
C.
Run the CREATE TRIGGER statement and create nested triggers.
D.
Run the TRIGGER_NESTLEVEL statement.
Explanation:
You can use the CREATE TRIGGER statement to create nested triggers. A nested trigger is initiated by another trigger.
Incorrect Answers:
A, B: The nested trigger option of the sp_configure command specifies whether nested triggers are permitted. Setting this option to 0 disables nested triggers while 1 enables nested triggers. By default nested triggers are enabled.
D: The TRIGGER_NESTLEVEL returns the number of triggers executed for the statement that fired the trigger.
Reference:
Microsoft SQL Server 2005 Books Online (2007), Index: nested DML triggers Microsoft SQL Server 2005 Books Online (2007), Index: nested triggers option Microsoft SQL Server 2005 Books Online (2007), Index: TRIGGER_NESTLEVEL function