12. 触发器
|
本章主题 l 触发器概述 l 创建触发器 l 一些限制 l DELETED表和INSERTED表
学习如何使用SQL来写trigger，触发器有时不得不用！ |
12.1. 触发器概述
触发器（trigger）是指在用户要对某一表内的数据做插入、更新、删除时被触发执行的一段程序。
通常使用触发器来检查用户对表的操作是否合乎整个应用系统的需求和合乎商业规则以维持表内数据的完整性和正确性。
12.2. 创建触发器
CREATE TRIGGER 触发器名字
ON 表名
FOR {INSERT| UPDATE | DELETE}
AS 程序行
INSERT、UPDATE、 DELETE 分别针对插入触发器、更新触发器、删除触发器。
范例一：示范创建一更新触发器，当有人更新salary列时，触发器将启动执行，还原对salary列的操作，并将该操作者的名称和操作时间记录在modify_log表内。
CREATE TRIGGER tri_employee
ON employee
FOR UPDATE
AS
IF UPDATE(salary)
BEGIN
ROLLBACK TRANSACTION --回滚事务，将对salary的更改还原
INSERT INTO modify_log VALUES (CURRENT_USER,GETDATE())
END
12.3. 一些限制
在触发器内使用SQL命令有一些限制,下列SQL命令不能出现在触发器内：
l 所有数据库对象生成命令，如CREATE TABLE、CREATE INDEX等。
l 所有数据库对象结构修改命令，如ALTER TABLE、ALTER DATABASE等。
l 创建临时表命令
l TRUNCATE TABLE命令
l 所有DROP命令
12.4. DELETED表和INSERTED表
l 当执行DELETE命令删除表数据时，被删除的数据存放在DELETED系统临时保存表内。
l 当执行INSERT命令插入表数据时，被插入的数据存放在INSERTED系统临时保存表内。
l DELETED及INSERTED表内容不能更改。
l 对UPDATE命令而言，系统会先将被操作的数据先删除，然后插入经过修改过的数据。其中删除的数据放在DELETED表内，插入的数据放在INSERTED表内。（理解这点非常重要！）
范例二：让用户一次仅能删除一条人事数据。
CREATE TRIGGER tri_emp
ON employee
FOR DELETE
AS
DECLARE＠row_cnt INT
SELECT @row_cnt=count(*) FROM DELETED
IF @row_cnt>1
BEGIN
PRINT ‘此删除操作可能会删除多条人事表数据！！！’
ROLLBACK TRANSACTION
END
12.5. 更改触发器名称
EXEC sp_rename 旧触发器名，新触发器名
12.6. 删除触发器
DROP TRIGGER触发器名
12.7. 触发器示例
12.7.1. INSERT触发器
/****** Object: Trigger dbo.sha_dgd_mx_insert Script Date: 2004-04-10 13:18:10 ******/
CREATE TRIGGER [sha_dgd_mx_insert]
ON [dbo].[sha_dgd_mx]
FOR INSERT
AS
BEGIN
/*====================================================*/
/*触发器：增加*/
/*更新纱预算明细表(sha_ysb_mx)*/
/*当预算表号、预算表行号相等时，更新已订购数量dg_sl＝原数量＋新订购数量*/
/*====================================================*/
UPDATE sha_ysb_mx
SET dg_sl=ISNULL(dg_sl,0)+ISNULL(i.weight,0)
from sha_ysb_mx s inner join inserted i
on s.sha_ysb_id=i.sha_ysb_id AND s.shaysmx_sn=i.shaysmx_sn
END
12.7.2. DELETE触发器
/****** Object: Trigger dbo.sha_dgd_mx_delete Script Date: 2004-04-10 13:18:10 ******/
CREATE TRIGGER [sha_dgd_mx_delete] ON [dbo].[sha_dgd_mx]
FOR DELETE
AS
BEGIN
/*====================================================*/
/*触发器：删除*/
/*更新纱预算明细表(sha_ysb_mx)*/
/*当预算表号、预算表行号相等时，更新已订购数量dg_sl＝原数量-删除的订购数量*/
/*====================================================*/
UPDATE sha_ysb_mx
SET dg_sl=ISNULL(dg_sl,0)-ISNULL(d.weight,0)
from sha_ysb_mx s inner join deleted d
on s.sha_ysb_id=d.sha_ysb_id AND s.shaysmx_sn=d.shaysmx_sn
END
12.7.3. UPDATE触发器
/****** Object: Trigger dbo.sha_dgd_mx_update Script Date: 2004-04-10 13:18:11 ******/
CREATE TRIGGER [sha_dgd_mx_update] ON [dbo].[sha_dgd_mx]
FOR UPDATE
AS
BEGIN
/*====================================================*/
/*触发器：更新*/
/*更新纱预算明细表(sha_ysb_mx)*/
/*当预算表号、预算表行号相等时，更新已订购数量dg_sl*/
/*====================================================*/
if update(shaysmx_sn) or update(weight)
begin
--先还原删除的订购数量
UPDATE sha_ysb_mx
SET dg_sl=ISNULL(dg_sl,0)-ISNULL(d.weight,0)
from sha_ysb_mx s inner join deleted d
on s.sha_ysb_id=d.sha_ysb_id AND s.shaysmx_sn=d.shaysmx_sn
--再加上新的订购数量
UPDATE sha_ysb_mx
SET dg_sl=ISNULL(dg_sl,0)+ISNULL(i.weight,0)
from sha_ysb_mx s inner join inserted i
on s.sha_ysb_id=i.sha_ysb_id AND s.shaysmx_sn=i.shaysmx_sn
end
END
|
第12章 结束 |
