觸發器是一種特殊的預存程序,無法顯式呼叫,而是在對表執行 INSERT、UPDATE、DELETE 操作時自動觸發。常見三類:INSERT、UPDATE、DELETE 觸發器。
假設有兩張表:Student(學生主檔)與 BorrowRecord(借書記錄)。需求:
CREATE TRIGGER truStudent
ON Student
FOR UPDATE
AS
IF UPDATE(StudentID)
BEGIN
UPDATE BorrowRecord
SET StudentID = i.StudentID
FROM BorrowRecord br
INNER JOIN Deleted d ON br.StudentID = d.StudentID
INNER JOIN Inserted i ON d.StudentID = i.StudentID;
END;
DELETE 或 UPDATE 而被移除的舊資料列。INSERT 或 UPDATE 而新增的新資料列。CREATE TRIGGER trdStudent
ON Student
FOR DELETE
AS
DELETE FROM BorrowRecord
WHERE StudentID IN (SELECT StudentID FROM Deleted);
SET NOCOUNT ON 減少網路傳輸。ROLLBACK 回滾交易。