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.

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

Explanation:
In the code with answer option D, the deleted table is queried to see if it holds any data. The deleted
table is a virtual table that exists
immediately after the DELETE statement in a trigger. It will hold the deleted row or rows. The
COUNT(*) is counting the number of rows in the
table, and if any rows are present, the trigger will raise the error and roll back the transaction. This
method is useful if you want to prevent
more than a specific count (such as more than 5 customers) from being deleted.