版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
1、SQL触发器实例讲解 SQL触发器实例讲解(本文是来自百度文库)备注:本人建了一个站特价汇,我想记录每个商品的点击量,然后按照点击量来牌名商品,想要提高效率,所以必须得用触发器,下面是本人在百度文库中的找到的学习资料,分享下给大家。 定义: 何为触发器?在SQL Server里面也就是对某一个表的一定的操作,触发某种条件,从而执行的一段程序。触发器是一个特殊的存储过程。 常见的触发器有三种:分别应用于Insert , Update , Dele
2、te 事件。 我为什么要使用触发器?比如,这么两个表: Create Table Student( -学生表 StudentID
3、60;int primary key, -学号 . ) Create Table BorrowRecord(
4、160; -学生借书记录表 BorrowRecord int identity(1,1), -流水号 StudentID
5、 int , -学号 BorrowDate datetime,
6、0; -借出时间 ReturnDAte Datetime, -归还时间
7、; . ) 用到的功能有: 1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号); 2.如果该学生已经毕业,我希望删除他的学号的同时,也删除它的借书记录。 &
8、#160; 等等。 这时候可以用到触发器。对于1,创建一个Update触发器: Create Trigger truStudent On Student
9、60; -在Student表中创建触发器 for Update
10、160; -为什么事件触发 As
11、0;-事件触发后所要做的事情 if Update(StudentID) begin Update BorrowRecord
12、0; Set StudentID=i.StudentID From BorrowRecord br , Deleted d ,Inserted i -Deleted和
13、Inserted临时表 Where br.StudentID=d.StudentID end
14、; 理解触发器里面的两个临时的表:Deleted , Inserted 。注意Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。 一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息,分别是:
15、160; 虚拟表Inserted 虚拟表Deleted 在表记录新增时 存放新增的记录
16、60; 不存储记录 修改时 存放用来更新的新记录
17、60; 存放更新前的记录 删除时 不存储记录
18、; 存放被删除的记录 一个Update 的过程可以看作为:生成新的记录到Inserted表,复制旧的记录到Deleted表,然后删除Student记录并写入新纪录。 对于2,创建一个Delete触发器
19、 Create trigger trdStudent On Student for Delete As Delete BorrowRecor
20、d From BorrowRecord br , Delted d Where br.StudentID=d.StudentID 从这两个例子我们可以看到了触发器的关键:A.2个临时的表;B.触发机制。 SQL触发器实例2&
21、#160;/* 建立虚拟测试环境,包含:表卷烟库存表,表卷烟销售表。 请大家注意跟踪这两个表的数据,体会触发器到底执行了什么业务逻辑,对数据有什么影响。 为了能更清晰的表述触发器的作用,表结构存在数据冗余,且不符合第三范式,这里特此说明。 */ USE Master GO IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = U AND NAME
22、;= 卷烟库存表) DROP TABLE 卷烟库存表 GO IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = U AND NAME = 卷烟销售表) DROP TABLE 卷烟销售表 GO -业务规则:销售金额 = 销售数量 * 销售单价 业务规则。
23、160;CREATE TABLE 卷烟销售表 ( 卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL, 购货商 VARCHAR(40) NULL, 销售数量 INT NULL, 销售单价 MONEY NULL, 销售金额 MONEY NULL ) GO -业务规则:库存金额 = 库存数量 * 库
24、存单价 业务规则。 CREATE TABLE 卷烟库存表 ( 卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL, 库存数量 INT NULL, 库存单价 MONEY NULL, 库存金额 MONEY NULL ) GO -创建触发器,示例1 /* 创建触发器T_INSERT_卷烟库存表,这个触发器较简单。 说明:
25、 每当卷烟库存表发生 INSERT 动作,则引发该触发器。 触发器功能: 强制执行业务规则,保证插入的数据中,库存金额 = 库存数量 * 库存单价。 注意: INSERTED、DELETED为系统表,不可创建、修改、删除,但可以调用。 重要: 这两个系统表的结构同插入数据的表的结构。 */ IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE
26、;XTYPE = TR AND NAME = T_INSERT_卷烟库存表) DROP TRIGGER T_INSERT_卷烟库存表 GO CREATE TRIGGER T_INSERT_卷烟库存表 ON 卷烟库存表 FOR INSERT AS -提交事务处理 BEGIN TRANSACTION -强制执行下列语句,保证业务规则 UPDATE 卷烟库存表
27、160;SET 库存金额 = 库存数量 * 库存单价 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 from INSERTED) COMMIT TRANSACTION GO /* 针对卷烟库存表,插入测试数据: 注意,第一条数据(红塔山新势力)中的数据符合业务规则, 第二条数据(红塔山人为峰)中,库存金额空,不符合业务规则, 第三条数据(云南映像)中,库存金额不等于库存数量乘以库存单价,不
28、符合业务规则。 第四条数据库存数量为0。 请注意在插入数据后,检查卷烟库存表中的数据是否 库存金额 = 库存数量 * 库存单价。 */ INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额) SELECT 红塔山新势力,100,12,1200 UNION ALL SELECT 红塔山人为峰,100,22,NULL UNION ALL SELECT 云南映像,100,
29、60,500 UNION ALL SELECT 玉溪,0,30,0 GO -查询数据 SELECT * FROM 卷烟库存表 GO /* 结果集 RecordId 卷烟品牌 库存数量 库存单价 库存金额 - - - - - 1 红塔山新势力 100 12.0000 1200.0000 2 红塔山人为
30、峰 100 22.0000 2200.0000 3 云南映像 100 60.0000 6000.0000 4 玉溪 0 30.0000 .0000 (所影响的行数为 4 行) */ -触发器示例2 /* 创建触发器T_INSERT_卷烟销售表,该触发器较复杂。 说明: 每当卷烟库存表发生 INSERT 动作,则引发该触发器。 触发器功能:
31、;实现业务规则。 业务规则: 如果销售的卷烟品牌不存在库存或者库存为零,则返回错误。 否则则自动减少卷烟库存表中对应品牌卷烟的库存数量和库存金额。 */ IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = TR AND NAME = T_INSERT_卷烟销售表) DROP TRIGGER T_INSERT_卷烟销售表 GO
32、 CREATE TRIGGER T_INSERT_卷烟销售表 ON 卷烟销售表 FOR INSERT AS BEGIN TRANSACTION -检查数据的合法性:销售的卷烟是否有库存,或者库存是否大于零 IF NOT EXISTS ( SELECT 库存数量 FROM 卷烟库存表 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM
33、160;INSERTED) ) BEGIN -返回错误提示 RAISERROR(错误!该卷烟不存在库存,不能销售。,16,1) -回滚事务 ROLLBACK RETURN END IF EXISTS ( SELECT 库存数量 FROM 卷烟库存表 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) AND 库存数量
34、;<= 0 ) BEGIN -返回错误提示 RAISERROR(错误!该卷烟库存小于等于0,不能销售。,16,1) -回滚事务 ROLLBACK RETURN END -对合法的数据进行处理 -强制执行下列语句,保证业务规则 UPDATE 卷烟销售表 SET 销售金额 = 销售数量 * 销售单价 WHERE 卷烟品牌 IN (SELECT 卷烟品牌
35、160;FROM INSERTED) DECLARE 卷烟品牌 VARCHAR(40) SET 卷烟品牌 = (SELECT 卷烟品牌 FROM INSERTED) DECLARE 销售数量 MONEY SET 销售数量 = (SELECT 销售数量 FROM INSERTED) UPDATE 卷烟库存表 SET 库存数量 =
36、库存数量 - 销售数量, 库存金额 = (库存数量 - 销售数量)*库存单价 WHERE 卷烟品牌 = 卷烟品牌 COMMIT TRANSACTION GO -请大家自行跟踪卷烟库存表和卷烟销售表的数据变化。 -针对卷烟销售表,插入第一条测试数据,该数据是正常的。 INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) SELECT 红塔山新势力,某购货商,
37、10,12,1200 GO -针对卷烟销售表,插入第二条测试数据,该数据 销售金额 不等于 销售单价 * 销售数量。 -触发器将自动更正数据,使 销售金额 等于 销售单价 * 销售数量。 INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) SELECT 红塔山人为峰,某购货商,10,22,2000 GO -针对卷烟销售表,插入第三条测试数据,该数据中的卷烟品牌在&
38、#160;卷烟库存表中找不到对应。 -触发器将报错。 INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) SELECT 红河V8,某购货商,10,60,600 GO /* 结果集 服务器: 消息 50000,级别 16,状态 1,过程 T_INSERT_卷烟销售表,行 15 错误!该卷烟不存在库存,不能销售。 */ -针对卷烟销售表,插入第三条测试数据,该数据中的卷烟品牌在
39、 卷烟库存表中库存为0。 -触发器将报错。 INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) SELECT 玉溪,某购货商,10,30,300 GO /* 结果集 服务器: 消息 50000,级别 16,状态 1,过程 T_INSERT_卷烟销售表,行 29 错误!该卷烟库存小于等于0,不能销售。 */ -查询数据 SELECT *
40、;FROM 卷烟库存表 SELECT * FROM 卷烟销售表 GO /* 补充: 1、本示例主要通过一个简单的业务规则实现来进行触发器使用的说明,具体的要根据需要灵活处理; 2、关于触发器要理解并运用好 INSERTED ,DELETED 两个系统表; 3、本示例创建的触发器都是 FOR INSERT ,具体的语法可参考: /
41、60; Trigger语法
42、/CREATE TRIGGER trigger_name ON table | view WITH ENCRYPTION -用于加密触发器 FOR | AFTER | INSTEAD OF INSERT , UPDATE
43、0; WITH APPEND NOT FOR REPLICATION AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n | IF ( COLUMNS_UPDATED (&
44、#160;) bitwise_operator updated_bitmask ) comparison_operator column_bitmask .n sql_statement .n 4、关于触发器,还应该注意 (1)、DELETE 触发器不能捕获 TRUNCATE TABLE 语句。 (2)、
45、触发器中不允许以下 Transact-SQL 语句: ALTER DATABASE CREATE DATABASE DISK INIT DISK RESIZE DROP DATABASE LOAD DATABASE LOAD LOG RECONFIGURE RESTORE DATABASE RESTORE LOG (3)、触发器最多可以嵌套 32 层。
46、60;*/ -修改触发器 -实质上,是将 CREATE TRIGGER . 修改为 ALTER TRIGGER .即可。 -删除触发器 DROP TRIGGER xxx GO -删除测试环境 DROP TABLE 卷烟库存表 GO DROP TABLE 卷烟销售表 GO DROP TRIGGER T_INSERT_卷烟库存表 G
47、O DROP TRIGGER T_INSERT_卷烟销售表 GO # 触发器的基础知识和例子 :create trigger tr_name on table/view for | after | instead of update,insert,delete with encryption as batch | if update
48、160;(col_name) and|or update (col_name) 说明: 1 tr_name :触发器名称 2 on table/view :触发器所作用的表。一个触发器只能作用于一个表 3 for 和after :同义 4 after 与instead of :sql 2000新增项目afrer 与 instead of
49、;的区别 After 在触发事件发生以后才被激活,只可以建立在表上 Instead of 代替了相应的触发事件而被执行,既可以建立在表上也可以建立在视图上 5 insert、update、delete:激活触发器的三种操作,可以同时执行,也可选其一 6 if update (col_name):表明所作的操作对指定列是否有影响,有影响,则激活触发器。此外,因为delete 操作只对行有影响, 所以如果使用delete操作就不能用这条语句了(虽然使用也不出错,但是不能
50、激活触发器,没意义)。 7 触发器执行时用到的两个特殊表:deleted ,inserted deleted 和inserted 可以说是一种特殊的临时表,是在进行激活触发器时由系统自动生成的,其结构与触发器作用的表结构是一 样的,只是存放 的数据有差异。 续 下面表格说明deleted 与inserted 数据的差异 deleted 与inserted 数据的差异 Inserted 存放进行insert和update
51、0;操作后的数据 Deleted 存放进行delete 和update操作前的数据 注意:update 操作相当于先进行delete 再进行insert ,所以在进行update操作时,修改前的数据拷贝一条到deleted 表中,修改后 的数据在存到触发器作用的表的同时,也同时生成一条拷贝到insered表中 / CREATE TRIGGER TRIGGER admixture_receive_log ON dbo.chl_lydj
52、160;FOR UPDATEASbegin declare djsfxg char(10) declare wtbh char(20) select wtbh=wtbh from inserted
53、60; update ly_tzk set djsfxg='已修改' where wtbh=wtbhendif (select data_sfjl from t_logsetup)='是'begindeclare oldcjmc char (100) declare oldlyrq datetime
54、declare oldbzbh char (60) declare oldzl char (20)declare olddj char (10) declare newcjmc char (100)
55、;declare newlyrq datetimedeclare newbzbh char (60) declare newzl char (20)declare newdj char (10)
56、; declare xgr char (20) select oldcjmc=cjmc,oldlyrq=lyrq,oldbzbh=bzbh,oldzl=zl,olddj=dj from deleted
57、0;select newcjmc=cjmc,newlyrq=lyrq,newbzbh=bzbh,newzl=zl,newdj=dj from inserted select xgr=xgr from t_modifyuser where wtbh=wtbh &
58、#160; if oldcjmc<>newcjmc begin
59、160; insert into t_modifylog (wtbh, mod_time, mod_table, mod_field, ori_value, now_value, mod_people) values
60、; (wtbh,getdate(), 'chl_lydj','cjmc', oldcjmc, newcjmc, xgr) end end/修改时,直接把create改为alter即可 / CREATE TRIGGER TRIGGER ly_tzk_syf ON dbo.ly_tzk&
61、#160;FOR insert ASbegin declare clmc char(100) declare dwbh char(100) declare syf char(100) declare dwgcbh char(100) declare wtbh c
62、har(50) declare dj_1 money declare feiyong_z money declare feiyong_xf money declare feiyong_sy money
63、160; declare dj char(20) select wtbh=wtbh , clmc=clmc , dwbh=dwbh ,syf=syf from inserted select dj=dj from feiha
64、o_bz where clmc=clmc select feiyong_z=feiyong_z, feiyong_xf=feiyong_xf, feiyong_sy=feiyong_sy from gongchengxinxi where dwgcbh=dwbh set dj_1=convert(m
65、oney ,dj) if dj_1 <>0 begin set feiyong_xf=feiyong_xf+dj_1
66、60; set feiyong_sy=feiyong_sy-dj_1 update ly_tzk set syf=dj where wtbh=wtbh update gongchengxinxi set feiyong_xf=feiyong_xf, feiyong_sy=feiyong_sy where dwgcbh=dwbh &
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 电机与电器基础考试考核试卷
- 家用通风设备市场渠道拓展考核试卷
- 健身指导与服务考核试卷
- 竹材采运技术创新与研发考核试卷
- 挤压盘课程设计总结
- ppp项目课程设计
- 技术教学课程设计
- 别墅消防课程设计
- 口语交际能力课程设计
- 个别幼儿的课程设计
- 房建施工资料模板
- 2023年小学综合实践活动基本功大赛理论测试卷
- 甲状腺结节科普课件
- 第二章(4)T型账户和试算平衡
- 初中音乐课的实践教学经验总结
- 《全面质量管理》课件
- 新教材外研版高中英语选择性必修第一册各单元重点语法归纳总结.文档
- 金融风险管理 课件全套(殷平生)第1-12章 金融风险管理概述-大数据发展与金融风险管理
- 河北病历报告查询
- 患者隐私保护培训课件1
- 健康管理与咨询服务平台可行性报告
评论
0/150
提交评论