Exam 98-364 Topic 4 Question 91 Discussion

Actual exam question for Microsoft's 98-364 exam
Question #: 91
Topic #: 4

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

Suggested Answer: B Vote an answer

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.

by Ruth at Jan 25, 2025, 09:55 AM

Limited Time Offer

15%

Off

Get Premium 98-364 Questions as Interactive Self Test Engine or PDF

Comments

0 Happy Clients

0 Shares

0 Demo Downloads

10 Years in Business