Exam 98-364 topic 1 question 155 discussion

Actual exam question from Microsoft's 98-364

Question #: 155
Topic #: 1

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

Show Suggested Answer

Suggested Answer: BD 🗳️

by rogeriosolano at June 28, 2022, 1:17 p.m.

Comments

Submit Cancel

rogeriosolano

2 years, 10 months ago

Selected Answer: BD

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.

upvoted 1 times

...

Exam 98-364 All Questions

View all questions & answers for the 98-364 exam

Exam 98-364 topic 1 question 155 discussion

Comments

rogeriosolano

SY0-701