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 modifiedfor 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 triggerto 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 INSTEADOF 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 shouldnot
be executed when a replication agent modifies the table that is
involved in the trigger.