Database Fundamentals — Question 139

You are a database developer for a database named Customers hosted on a SQL Server 2008 server.
Recently, several customers were deleted from the Customers database.
To ensure this is not repeated in future, you have decided to create a DML trigger to prevent it.
What code will create the trigger to meet your goals? Each correct answer represents a complete solution. (Choose all that apply.)

Answer options

• A. CREATE TRIGGER trgDeleteCustomer ON dbo.Customers BEFORE DELETE AS RAISERROR('Customers cannot be deleted. An error has been logged', 16, 10) WITH LOG ROLLBACK TRANSACTION
• B. CREATE TRIGGER trgDeleteCustomer ON dbo.Customers AFTER DELETE AS RAISERROR('Customers cannot be deleted. An error has been logged', 16, 10) WITH LOG ROLLBACK TRANSACTION
• C. CREATE TRIGGER trgDeleteCustomer ON dbo.Customers AFTER DELETE AS IF(SELECT COUNT(*) FROM DELETED) > 1 BEGIN RAISERROR('Customers cannot be deleted. An error has been logged', 16, 10) WITH LOG ROLLBACK TRANSACTION END
• D. CREATE TRIGGER trgDeleteCustomer ON dbo.Customers AFTER DELETE AS IF (SELECT COUNT(*) FROM DELETED) > 0 BEGIN RAISERROR('Customers cannot be deleted. An error has been logged', 16, 10) WITH LOG ROLLBACK TRANSACTION END

Correct answer: B, D

Explanation

Option B is correct because it uses an AFTER DELETE trigger to raise an error and roll back the transaction, preventing any deletion. Option D is also correct as it checks if any rows were deleted (COUNT > 0) and performs the same actions. Options A and C are incorrect; A uses BEFORE DELETE which is not valid in SQL Server for this purpose, and C restricts the error handling to only when more than one row is deleted, which does not encompass all deletion cases.