MSSQL

目录

DML触发器 1.AFTER触发器 2.INSTEAD OF触发器

---

---

触发器

为执行业务规则和保持数据完整性而提供的一种机制，可以在插入、更新、删除等前后自动触发。

DML触发器

AFTER触发器（FOR）

INSERT/UPDATE/DELETE等，INSTEAD OF触发器，约束之后激发

deleted表&inserted表，是由数据库引擎自动生成，与生成触发器基表结构一致。保存的数据对应如下表：

deletedinsertedupdate更新前更新后insert插入delete删除-- update()函数，检查更新列，指定更新相应列时更才更新 IF OBJECT_ID('dbo.tgr_voc', 'TR') IS NOT NULL DROP TRIGGER dbo.tgr_voc GO CREATE TRIGGER dbo.tgr_voc ON dbo.voc AFTER UPDATE AS IF UPDATE(item) OR UPDATE(quantity) BEGIN IF OBJECT_ID('dbo.voc_up', 'U') IS NOT NULL DROP TABLE dbo.voc_up SELECT * into dbo.voc_up FROM deleted ALTER TABLE dbo.voc_up ADD date_ datetime DEFAULT GETDATE() ALTER TABLE dbo.voc_up ADD note varchar(10) DEFAULT '更新前' UPDATE dbo.voc_up SET date_=GETDATE(), note='更新前' INSERT INTO dbo.voc_up(item, color, quantity, note) SELECT item, color, quantity, '更新后' AS note FROM inserted END GO UPDATE dbo.voc SET quantity=10 WHERE item='yizi' AND color='red' SELECT * FROM dbo.voc_up GO -- 设置First和Last触发器 EXEC sp_settriggerorder 'tgr_voc', 'first', 'UPDATE' GO -- 查询触发器是否为First/Last触发器 -- 第二个参数还可以是：ExecIsFirstDeleteTrigger,ExecIsFirstInsertTrigger,ExecIsLastUpdateTrigger,ExecIsLastDeleteTrigger,ExecIsLastInsertTrigger SELECT OBJECTPROPERTY(OBJECT_ID(N'tgr_voc'), 'ExecIsFirstUpdateTrigger') -- inserted表 INSERT INTO dbo.voc OUTPUT inserted.* VALUES ('yizi', 'red', 10) GO -- deleted表 DELETE FROM dbo.voc OUTPUT deleted.* WHERE item='yizi' AND quantity=10 GO

INSTEAD OF触发器

约束之前激发

优点：使不能更新的视图支持更新。基于多个基表的视图必须使用INSTEAD OF触发器来对多个表进行更新、插入和删除。

-- 如插入item，color已在，则更新quantity；如不，则插入 IF OBJECT_ID('dbo.tr_instead_insert_duplicates', 'TR') IS NOT NULL DROP TRIGGER dbo.tr_instead_insert_duplicates GO CREATE TRIGGER dbo.tr_instead_insert_duplicates ON dbo.voc INSTEAD OF INSERT AS BEGIN IF EXISTS( SELECT * FROM dbo.voc v, inserted i WHERE v.color = i.color AND v.item = i.item) BEGIN UPDATE dbo.voc SET quantity = dbo.voc.quantity + inserted.quantity FROM inserted WHERE dbo.voc.color = inserted.color AND dbo.voc.item = inserted.item END ELSE BEGIN INSERT INTO dbo.voc SELECT * FROM inserted END END GO INSERT INTO dbo.voc VALUES ('yizi', 'red', 5) INSERT INTO dbo.voc -- 单行插入有效 VALUES ('yizi', 'yellow', 10), ('yizi', 'yellow', 10) GO SELECT * FROM dbo.voc

DDL触发器

主要响应CREATE/ALTER/DROP开头的语句。

用于执行管理任务，如审核和控制数据库操作。

只能AFTER。

启动&禁用触发器

-- 禁用触发器 DISABLE TRIGGER dbo.tr_instead_insert_duplicates ON dbo.voc GO ALTER TABLE dbo.voc DISABLE TRIGGER tr_instead_insert_duplicates -- 不能用dbo. GO -- 启动 ALTER TABLE dbo.voc ENABLE TRIGGER tr_instead_insert_duplicates GO