Click the Exhibit button.
You are developing a database using Microsoft SQL Server 2008. The database contains the tables
shown in the exhibit.
You are required to prevent parts from being deleted if they belong to a kit. If a part belongs to a kit,
the
delete should not occur and the IsDeleted column for the row should be changed to ‘True’. Parts can
be
deleted if they do not belong to a kit.
You have the following TransactSQL
statement to be used in a trigger:
UPDATE p
SET IsDeleted = 1
FROM KitPart kp
JOIN deleted d ON kp.PartID = d.PartID
JOIN Part p ON kp.PartID = p.PartID;
DELETE FROM p
FROM Part p
JOIN deleted d ON p.PartID = d.PartID
LEFT OUTER JOIN KitPart kp ON p.PartID = kp.PartID
WHERE kp.KitID IS NULL;
You need to implement the TransactSQL
statement in a trigger. Which trigger syntax should you use?
A.
CREATE TRIGGER tr_Part_d ON Part
AFTER DELETE AS
BEGIN
…
END
B.
CREATE TRIGGER tr_Part_d ON Part
INSTEAD OF DELETE AS
BEGIN
…
END
C.
CREATE TRIGGER tr_KitPart_d ON KitPart
AFTER DELETE AS
BEGIN
…
END
D.
CREATE TRIGGER tr_KitPart_d ON KitPart
INSTEAD OF DELETE AS
BEGIN
…
END