You are a database developer for a database named Sales hosted on a SQL Server 2008 server. The
Sales database includes the Exams and
ExamQuestions tables. If any questions are modified for an exam, you need to ensure that the
ExamModifiedDate in the Exams table is
updated with the date and time of the update. The ExamQuestions table includes an INSTEAD OF
trigger that records when and who last
modified each question. Therefore, you decide to create a trigger. You only want the trigger to fire if
a constraint violation has not occurred.
Which of the following code will create the trigger to meet your goals?

A.
CREATE TRIGGER trgExamQuestionsModified
ON dbo.ExamQuestions
INSTEAD OF UPDATE NOT FOR REPLICATION
AS
UPDATE Exams
SET ExamModifiedDate = GetDate()
FROM inserted
WHERE inserted.ExamID = Exams.ExamID

B.
CREATE TRIGGER trgExamQuestionsModified
ON dbo.ExamQuestions
AFTER UPDATE NOT FOR REPLICATION
AS
UPDATE Exams
SET ExamModifiedDate = GetDate()
FROM inserted
WHERE inserted.ExamID = Exams.ExamID

C.
CREATE TRIGGER trgExamQuestionsModified
ON dbo.ExamQuestions
AFTER UPDATE FOR REPLICATION
AS
UPDATE Exams
SET ExamModifiedDate = GetDate()
FROM inserted
WHERE inserted.ExamID = Exams.ExamID

D.
CREATE TRIGGER trgExamQuestionsModified
ON dbo.Exams
AFTER UPDATE NOT FOR REPLICATION
AS
UPDATE Exams
SET ExamModifiedDate = GetDate()
FROM inserted
WHERE inserted.ExamID = Exams.ExamID

Explanation:
The syntax The DML trigger is created on the ExamQuestions table as it should be. AFTER UPDATE is
used to ensure that the trigger
will only fire after constraints are checked and succeed. In other words, if the UPDATE fails because
it does not meet the constraint checks,
the trigger will not fire. In contrast, the INSTEAD OF trigger will fire even if the UPDATE would violate
constraints since constraints are not
checked.
While replication is not specifically mentioned in the question, you can specify NOT FOR
REPLICATION if the table(s) are involved in replication.
The NOT FOR REPLICATION clause indicates that the trigger should not be executed when a
replication agent modifies the table that is
involved in the trigger.