觸發器是隨表數據變更(INSERT/UPDATE/DELETE)自動執行的特殊存儲過程。本文系統講解觸發器類型、魔術表、創建語法、實戰案例(級聯操作、列級校驗、日誌記錄)及管理操作,助你掌握這項強大的數據完整性利器。
inserted:新增/修改後的數據deleted:刪除/修改前的數據ROLLBACK 撤銷操作。
CREATE TRIGGER tgr_classes_insert ON classes FOR INSERT AS DECLARE @id INT, @name VARCHAR(20), @temp INT; SELECT @id = id, @name = name FROM inserted; SET @name = @name + CAST(@id AS VARCHAR); SET @temp = @id / 2; INSERT INTO student VALUES (@name, 18 + @id, @temp, @id); PRINT '添加學生成功!';
CREATE TRIGGER tgr_classes_delete ON classes FOR DELETE AS
PRINT '備份數據中……';
IF OBJECT_ID('classesBackup', 'U') IS NOT NULL
INSERT INTO classesBackup SELECT name, createDate FROM deleted;
ELSE
SELECT * INTO classesBackup FROM deleted;
PRINT '備份數據成功!';
CREATE TRIGGER tgr_classes_update ON classes FOR UPDATE AS
DECLARE @oldName VARCHAR(20), @newName VARCHAR(20);
SELECT @oldName = name FROM deleted;
IF EXISTS (SELECT * FROM student WHERE name LIKE '%' + @oldName + '%')
BEGIN
SELECT @newName = name FROM inserted;
UPDATE student SET name = REPLACE(name, @oldName, @newName)
WHERE name LIKE '%' + @oldName + '%';
PRINT '級聯修改成功!';
END
ELSE PRINT '無需修改 student 表!';
CREATE TRIGGER tgr_classes_update_column ON classes FOR UPDATE AS
IF UPDATE(createDate)
BEGIN
RAISERROR('系統提示:班級創建時間不能修改!', 16, 11);
ROLLBACK TRAN;
END
CREATE TRIGGER tgr_classes_inteadOf ON classes INSTEAD OF DELETE AS DECLARE @id INT, @name VARCHAR(20); SELECT @id = id, @name = name FROM deleted; DELETE student WHERE cid = @id; DELETE classes WHERE id = @id; PRINT '刪除 [id:' + CAST(@id AS VARCHAR) + ', name:' + @name + '] 成功!';
CREATE TRIGGER tgr_message ON student AFTER INSERT, UPDATE AS
RAISERROR('tgr_message 觸發器被觸發', 16, 10);
CREATE TRIGGER tgr_valid_data ON student AFTER INSERT AS
DECLARE @age INT, @name VARCHAR(20);
SELECT @name = s.name, @age = s.age FROM inserted s;
IF @age < 18
BEGIN
RAISERROR('插入數據年齡不合規', 16, 1);
ROLLBACK TRAN;
END
CREATE TRIGGER tgr_student_log ON student AFTER INSERT, UPDATE, DELETE AS
IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
INSERT INTO log(action) VALUES('updated');
ELSE IF EXISTS (SELECT 1 FROM inserted)
INSERT INTO log(action) VALUES('inserted');
ELSE IF EXISTS (SELECT 1 FROM deleted)
INSERT INTO log(action) VALUES('deleted');
-- 修改觸發器
ALTER TRIGGER tgr_message ON student AFTER DELETE AS RAISERROR('...', 16, 10);
-- 禁用/啟用
DISABLE TRIGGER tgr_message ON student;
ENABLE TRIGGER tgr_message ON student;
-- 查詢觸發器信息
SELECT * FROM sys.triggers;
SELECT * FROM sys.objects WHERE type = 'TR';
-- 查看觸發事件
SELECT te.* FROM sys.trigger_events te JOIN sys.triggers t ON t.object_id = te.object_id
WHERE t.parent_class = 0 AND t.name = 'tgr_valid_data';
-- 查看創建腳本
EXEC sp_helptext 'tgr_message';