1、定义： 何为触发器？在 SQL Server 里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。 常见的触发器有三种：分别应用于 Insert , Update , Delete 事件。 我为什么要使用触发器？比如，这么两个表： Create Table Student( -学生表 StudentID int primary key, -学号 . ) Create Table BorrowRecord( -学生借书记录表 BorrowRecord int identity(1,1), -流水号 StudentID int , -学号 BorrowD
2、ate datetime, -借出时间 ReturnDAte Datetime, -归还时间 . ) 用到的功能有: 1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关 (也就是同时更改借书记录表的学号); 2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。 等等。 这时候可以用到触发器。对于 1，创建一个 Update 触发器： Create Trigger truStudent On Student -在 Student 表中创建触发器 for Update -为什么事件触发 As -事件触发后所要做的事情 if Update(StudentID) beg
3、in Update BorrowRecord Set StudentID=i.StudentID From BorrowRecord br , Deleted d ,Inserted i -Deleted 和 Inserted 临时表 Where br.StudentID=d.StudentID end 理解触发器里面的两个临时的表：Deleted , Inserted 。注意 Deleted 与 Inserted 分别表示触发事件的表“旧的一条记录”和“ 新的一条记录” 。 一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息，分别是： 虚拟表 Inserted 虚拟表 Deleted
4、在表记录新增时 存放新增的记录 不存储记录 修改时 存放用来更新的新记录 存放更新前的记录 删除时 不存储记录 存放被删除的记录 一个 Update 的过程可以看作为：生成新的记录到 Inserted 表，复制旧的记录到 Deleted 表，然后删除 Student 记录并写入新纪录。 对于 2，创建一个 Delete 触发器 Create trigger trdStudent On Student for Delete As Delete BorrowRecord From BorrowRecord br , Delted d Where br.StudentID=d.StudentID 从
5、这两个例子我们可以看到了触发器的关键：A.2 个临时的表；B.触发机制。 SQL 触发器实例 2/* 建立虚拟测试环境，包含：表卷烟库存表，表卷烟销售表。 请大家注意跟踪这两个表的数据，体会触发器到底执行了什么业务逻辑，对数据有什么影响。 为了能更清晰的表述触发器的作用，表结构存在数据冗余，且不符合第三范式，这里特此说明。 */ USE Master GO IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = U AND NAME = 卷烟库存表) DROP TABLE 卷烟库存表 GO IF EXISTS (SELECT NAME FROM
6、 SYSOBJECTS WHERE XTYPE = U AND NAME = 卷烟销售表) DROP TABLE 卷烟销售表 GO -业务规则：销售金额 = 销售数量 * 销售单价 业务规则。 CREATE TABLE 卷烟销售表 ( 卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL, 购货商 VARCHAR(40) NULL, 销售数量 INT NULL, 销售单价 MONEY NULL, 销售金额 MONEY NULL ) GO -业务规则：库存金额 = 库存数量 * 库存单价 业务规则。 CREATE TABLE 卷烟库存表 ( 卷烟品牌 VARCHAR(40)
7、 PRIMARY KEY NOT NULL, 库存数量 INT NULL, 库存单价 MONEY NULL, 库存金额 MONEY NULL ) GO -创建触发器，示例 1 /* 创建触发器T_INSERT_卷烟库存表，这个触发器较简单。 说明： 每当卷烟库存表发生 INSERT 动作，则引发该触发器。 触发器功能： 强制执行业务规则，保证插入的数据中，库存金额 = 库存数量 * 库存单价。 注意： INSERTED、DELETED为系统表，不可创建、修改、删除，但可以调用。 重要： 这两个系统表的结构同插入数据的表的结构。 */ IF EXISTS (SELECT NAME FROM SY
8、SOBJECTS WHERE XTYPE = TR AND NAME = T_INSERT_卷烟库存表) DROP TRIGGER T_INSERT_卷烟库存表 GO CREATE TRIGGER T_INSERT_卷烟库存表 ON 卷烟库存表 FOR INSERT AS -提交事务处理 BEGIN TRANSACTION -强制执行下列语句，保证业务规则 UPDATE 卷烟库存表 SET 库存金额 = 库存数量 * 库存单价 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 from INSERTED) COMMIT TRANSACTION GO /* 针对卷烟库存表，插入测试数据： 注
9、意，第一条数据（红塔山新势力）中的数据符合业务规则， 第二条数据（红塔山人为峰）中，库存金额空，不符合业务规则， 第三条数据（云南映像）中，库存金额不等于库存数量乘以库存单价，不符合业务规则。 第四条数据库存数量为 0。 请注意在插入数据后，检查卷烟库存表中的数据是否 库存金额 = 库存数量 * 库存单价。 */ INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额) SELECT 红塔山新势力,100,12,1200 UNION ALL SELECT 红塔山人为峰,100,22,NULL UNION ALL SELECT 云南映像,100,60,500 UNION AL
10、L SELECT 玉溪,0,30,0 GO -查询数据 SELECT * FROM 卷烟库存表 GO /* 结果集 RecordId 卷烟品牌 库存数量 库存单价 库存金额 - - - - - 1 红塔山新势力 100 12.0000 1200.0000 2 红塔山人为峰 100 22.0000 2200.0000 3 云南映像 100 60.0000 6000.0000 4 玉溪 0 30.0000 .0000 （所影响的行数为 4 行） */ -触发器示例 2 /* 创建触发器T_INSERT_卷烟销售表，该触发器较复杂。 说明: 每当卷烟库存表发生 INSERT 动作，则引发该触发器。
11、触发器功能： 实现业务规则。 业务规则: 如果销售的卷烟品牌不存在库存或者库存为零，则返回错误。 否则则自动减少卷烟库存表中对应品牌卷烟的库存数量和库存金额。 */ IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = TR AND NAME = T_INSERT_卷烟销售表) DROP TRIGGER T_INSERT_卷烟销售表 GO CREATE TRIGGER T_INSERT_卷烟销售表 ON 卷烟销售表 FOR INSERT AS BEGIN TRANSACTION -检查数据的合法性：销售的卷烟是否有库存，或者库存是否大于零 I
12、F NOT EXISTS ( SELECT 库存数量 FROM 卷烟库存表 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) ) BEGIN -返回错误提示 RAISERROR(错误！该卷烟不存在库存，不能销售。,16,1) -回滚事务 ROLLBACK RETURN END IF EXISTS ( SELECT 库存数量 FROM 卷烟库存表 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) AND 库存数量 newcjmcbegininsert into t_modifylog (wtbh, mod_time, mod_t
13、able, mod_field, ori_value, now_value, mod_people) values(wtbh,getdate(), chl_lydj,cjmc, oldcjmc, newcjmc, xgr)endend/修改时，直接把create改为alter 即可/CREATE TRIGGER TRIGGER ly_tzk_syf ON dbo.ly_tzk FOR insert ASbegindeclare clmc char(100) declare dwbh char(100) declare syf char(100) declare dwgcbh char(100)
14、 declare wtbh char(50) declare dj_1 money declare feiyong_z money declare feiyong_xf money declare feiyong_sy money declare dj char(20)select wtbh=wtbh , clmc=clmc , dwbh=dwbh ,syf=syf from insertedselect dj=dj from feihao_bz where clmc=clmcselect feiyong_z=feiyong_z, feiyong_xf=feiyong_xf, feiyong_
15、sy=feiyong_sy from gongchengxinxi where dwgcbh=dwbhset dj_1=convert(money ,dj)if dj_1 0 beginset feiyong_xf=feiyong_xf+dj_1set feiyong_sy=feiyong_sy-dj_1update ly_tzk set syf=dj where wtbh=wtbh update gongchengxinxi set feiyong_xf=feiyong_xf, feiyong_sy=feiyong_sy where dwgcbh=dwbh endelse update ly_tzk set syf=convert(char , 0.0) where wtbh=wtbhend/
