第24节 触发器与自动检查
存储过程由你EXEC调用;触发器由特定事件自动调用。这节只在一个独立的小表练习,避免与Book价格CHECK发生重复校验冲突。
1 先认识两类DML触发器
| 类型 | 什么时候运行 | 常见用途 |
|---|---|---|
| AFTER,也可写FOR | 操作及相关约束检查成功后 | 审计、补充业务检查 |
| INSTEAD OF | 替代原操作 | 控制视图写入、定制插入逻辑 |
SQL Server还有DDL、登录触发器,先知道分类。本节重点是表上的INSERT、UPDATE、DELETE事件。
2 inserted与deleted里是什么
| 表操作 | inserted | deleted |
|---|---|---|
| INSERT | 新行 | 空 |
| DELETE | 空 | 旧行 |
| UPDATE | 修改后的行 | 修改前的行 |
它们是触发器内部可查询的逻辑表,一条语句改10行时,里面可能有10行。不是把触发器自动调用10次。因此检查时用EXISTS或集合查询,不只取某一个变量值。
3 创建独立测试表与触发器
-- 故意不加价格CHECK:本节需要单独观察AFTER触发器的自定义提示
CREATE TABLE dbo.TriggerPractice(Id int PRIMARY KEY,Price decimal(8,2) NULL);
GO
CREATE TRIGGER dbo.TR_PricePractice
ON dbo.TriggerPractice
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT 1 FROM inserted WHERE Price<0)
BEGIN
-- 发现任意一行价格为负,撤销本次语句所属的事务
IF @@TRANCOUNT>0 ROLLBACK TRANSACTION;
RAISERROR(N'单价不能为负数',16,1); -- 16是严重级别,1是状态
RETURN; -- 报错后结束触发器
END;
END;
GOPrice为NULL时 <0不是TRUE,所以此触发器允许未知价。若业务不允许未知,还需NOT NULL或额外规则。
4 同时写两行,观察整条语句被拒绝
INSERT dbo.TriggerPractice(Id,Price) VALUES(1,20); -- 合法,保留
BEGIN TRY
-- 一行合法、一行非法:整条INSERT都应回滚,不只回滚-5那一行
INSERT dbo.TriggerPractice(Id,Price) VALUES(2,30),(3,-5);
END TRY
BEGIN CATCH
IF XACT_STATE()<>0 ROLLBACK TRANSACTION;
SELECT ERROR_MESSAGE() AS [预期的拒绝信息];
END CATCH;
SELECT * FROM dbo.TriggerPractice; -- 只剩Id=1、Price=205 CHECK与AFTER谁先执行
Book已有 CHECK(Price>=0)。如果直接在Book上建同范围AFTER触发器,负价会先被CHECK拒绝,AFTER根本不运行,所以看不到触发器自定义消息。
简单单行取值规则优先CHECK;需要复杂业务或记录修改再考虑触发器。这也是本节另建测试表的原因,不能把“约束拦截成功”说成“触发器已运行”。
INSTEAD OF在语义上替代原操作,如果需真正写入,触发器内部必须自行完成写入;之后实际基础表约束仍要满足,不是绕过完整性的捷径。
6 查看与管理
SELECT name,is_disabled FROM sys.triggers
WHERE parent_id=OBJECT_ID(N'dbo.TriggerPractice');
EXEC sp_helptext N'dbo.TR_PricePractice';
-- 管理自己的演示触发器,禁用不等于删除
DISABLE TRIGGER dbo.TR_PricePractice ON dbo.TriggerPractice;
ENABLE TRIGGER dbo.TR_PricePractice ON dbo.TriggerPractice;
-- 删除触发器定义,再清理本节演示表;不会影响Book或Sale
DROP TRIGGER dbo.TR_PricePractice;
DROP TABLE dbo.TriggerPractice;修改定义用ALTER TRIGGER,加完整新触发器体。UPDATE(列)检查列是否被语句涉及,不保证新值与旧值真的不同。
本节练习
不用执行,解释:为什么不能只从inserted读一个Price变量?UPDATE前的价格在哪?有CHECK时为何AFTER未必显示自定义提示?
对照答案
inserted可能多行,单变量会漏检查;旧值在deleted、新值在inserted;CHECK先拒绝时AFTER不会执行。
上一节:23 创建和调用存储过程 | 下一节:25 登录用户角色与权限 | 课程目录