《SQL触发器实例[互联网+]》由会员分享,可在线阅读,更多相关《SQL触发器实例[互联网+](11页珍藏版)》请在金锄头文库上搜索。
1、SQL触发器实例定义: 何为触发器?在SQL Server里面也就是对某一个表的一定的操作,触发某种条件,从而执行的一段程序。触发器是一个特殊的存储过程。 常见的触发器有三种:分别应用于Insert , Update , Delete 事件。 我为什么要使用触发器?比如,这么两个表: Create Table Student( -学生表 StudentID int primary key, -学号 . ) Create Table BorrowRecord( -学生借书记录表 BorrowRecord int identity(1,1), -流水号 StudentID int , -学号 Bo
2、rrowDate datetime, -借出时间 ReturnDAte Datetime, -归还时间 . ) 用到的功能有: 1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号); 2.如果该学生已经毕业,我希望删除他的学号的同时,也删除它的借书记录。 等等。 这时候可以用到触发器。对于1,创建一个Update触发器: Create Trigger truStudent On Student -在Student表中创建触发器 for Update -为什么事件触发 As -事件触发后所要做的事情 if Update(StudentID) begi
3、n 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 从这两个例子我们可以看到了触发器的关键:A.
5、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 SYSOBJECTS WHERE XTYPE
6、= 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) PRIMARY KEY NOT NULL, 库
7、存数量 INT NULL, 库存单价 MONEY NULL, 库存金额 MONEY NULL ) GO -创建触发器,示例1 /* 创建触发器T_INSERT_卷烟库存表,这个触发器较简单。 说明: 每当卷烟库存表发生 INSERT 动作,则引发该触发器。 触发器功能: 强制执行业务规则,保证插入的数据中,库存金额 = 库存数量 * 库存单价。 注意: INSERTED、DELETED为系统表,不可创建、修改、删除,但可以调用。 重要: 这两个系统表的结构同插入数据的表的结构。 */ IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = TR
8、 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 ALL SELECT 玉溪,0,30,0 GO -查询数
10、据 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 -检查数据的合法性:销售的卷烟是否有库存,或者库存是否大于零 IF NOT EXISTS ( SELECT 库存数量
12、FROM 卷烟库存表 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) ) BEGIN -返回错误提示 RAISERROR(错误!该卷烟不存在库存,不能销售。,16,1) -回滚事务 ROLLBACK RETURN END IF EXISTS ( SELECT 库存数量 FROM 卷烟库存表 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) AND 库存数量 = 0 ) BEGIN -返回错误提示 RAISERROR(错误!该卷烟库存小于等于0,不能销售。,16,1) -回滚事务 ROLLBACK RETURN END -对合法的数据进行处理 -强制执行下列语句,保证业务规则 UPDATE 卷烟销售表 SET 销售金额 = 销售数量 * 销售单价 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) DECLARE 卷烟品牌 VARCHAR(40) SET 卷烟品牌 = (SELECT 卷烟品牌 FR