![sqlserverSQL触发器的使用及语法_第1页](http://file2.renrendoc.com/fileroot_temp3/2021-10/23/46bdfd4c-c3e8-4356-97cf-53995431b749/46bdfd4c-c3e8-4356-97cf-53995431b7491.gif)
![sqlserverSQL触发器的使用及语法_第2页](http://file2.renrendoc.com/fileroot_temp3/2021-10/23/46bdfd4c-c3e8-4356-97cf-53995431b749/46bdfd4c-c3e8-4356-97cf-53995431b7492.gif)
![sqlserverSQL触发器的使用及语法_第3页](http://file2.renrendoc.com/fileroot_temp3/2021-10/23/46bdfd4c-c3e8-4356-97cf-53995431b749/46bdfd4c-c3e8-4356-97cf-53995431b7493.gif)
下载本文档
版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
1、学生表学号学生借书记录表流水号学号借出时间归还时间定义: 何为触发器?在 SQL Server 里面也就是对某一个表的一定的操作, 触发某种条件, 从而执行的一段程序。触发器是一个特殊的存储过程。常见的触发器有三种:分别应用于Insert , Update , Delete事件。我为什么要使用触发器?比如,这么两个表:Create Table Student( StudentID int primary key,Create Table BorrowRecord( BorrowRecord int identity(1,1), StudentID int ,-BorrowDate dateti
2、me, ReturnDAte Datetime,用到的功能有 :1. 如果我更改了学生的学号 ,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号 );2. 如果该学生已经毕业,我希望删除他的学号的同时,也删除它的借书记录。这时候可以用到触发器。对于1,创建一个 Update 触发器:Create Trigger truStudentOn Studentfor UpdateAsif Update(StudentID) begin在 Student 表中创建触发器 为什么事件触发 事件触发后所要做的事情Update BorrowRecord和 Inserted 临时表Set S
3、tudentID=i.StudentIDFrom BorrowRecord br , Deleted d ,Inserted i -DeletedWhere br.StudentID=d.StudentID end理解触发器里面的两个临时的表: Deleted , Inserted 。注意 Deleted 与 Inserted 分别 表示触发事件的表 “旧的一条记录 ”和 “新的一条记录 ”。一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息,分别是:虚拟表 Inserted 虚拟表 Deleted在表记录新增时修改时删除时存放新增的记录存放用来更新的新记录不存储记录不存储记录存放更新前
4、的记录 存放被删除的记录Deleted一个 Update 的过程可以看作为: 生成新的记录到 Inserted 表,复制旧的记录到 表,然后删除 Student 记录并写入新纪录。对于 2 ,创建一个 Delete 触发器Create trigger trdStudentOn Studentfor DeleteAsDelete BorrowRecordFrom BorrowRecord br , Delted dWhere br.StudentID=d.StudentID从这两个例子我们可以看到了触发器的关键: A.2 个临时的表; B. 触发机制。SQL 触发器实例 2USE MasterG
5、OAND NAME =IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = U卷烟库存表 )DROP TABLE 卷烟库存表GOAND NAME =IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = U卷烟销售表 )DROP TABLE 卷烟销售表GO- 业务规则:销售金额 = 销售数量 * 销售单价 业务规则。CREATE TABLE 卷烟销售表(卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL, 购货商 VARCHAR(40) NULL,销售数量 INT NU
6、LL,销售单价 MONEY NULL, 销售金额 MONEY NULL)GO- 业务规则:库存金额 = 库存数量 * 库存单价 业务规则。CREATE TABLE 卷烟库存表(卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL, 库存数量 INT NULL,库存单价 MONEY NULL, 库存金额 MONEY NULL)GOTRAND NAME- 创建触发器,示例 1IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = = T_INSERT_卷烟库存表 )DROP TRIGGER T_INSERT_ 卷烟库存表GOC
7、REATE TRIGGER T_INSERT_卷烟库存表ON 卷烟库存表FOR INSERTAS- 提交事务处理BEGIN TRANSACTION- 强制执行下列语句,保证业务规则UPDATE 卷烟库存表SET 库存金额 = 库存数量 * 库存单价WHERE 卷烟品牌 IN (SELECT 卷烟品牌 from INSERTED)COMMIT TRANSACTIONGOINSERT INTO 卷烟库存表 ( 卷烟品牌 ,库存数量 ,库存单价 ,库存金额 ) SELECT 红塔山新势力 ,100,12,1200 UNION ALLSELECT 红塔山人为峰 ,100,22,NULL UNION A
8、LLSELECT 云南映像 ,100,60,500 UNION ALLSELECT 玉溪 ,0,30,0GO- 查询数据SELECT * FROM 卷烟库存表GOTR AND NAME- 触发器示例 2IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = = T_INSERT_卷烟销售表 )DROP TRIGGER T_INSERT_ 卷烟销售表GOCREATE TRIGGER T_INSERT_ 卷烟销售表 ON 卷烟销售表FOR INSERTASBEGIN TRANSACTION- 检查数据的合法性:销售的卷烟是否有库存,或者库存是否大于
9、零IF NOT EXISTS (SELECT 库存数量FROM 卷烟库存表WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) )BEGIN- 返回错误提示RAISERROR( 错误!该卷烟不存在库存,不能销售。 ,16,1) - 回滚事务ROLLBACKRETURNENDIF EXISTS (SELECT 库存数量FROM 卷烟库存表WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) AND 库存数量 = 0)BEGIN- 返回错误提示RAISERROR( 错误!该卷烟库存小于等于 0 ,不能销售。 ,16,1)- 回滚事务 RO
10、LLBACK RETURN END - 对合法的数据进行处理- 强制执行下列语句,保证业务规则UPDATE 卷烟销售表SET 销售金额 = 销售数量 * 销售单价WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED)DECLARE 卷烟品牌 VARCHAR(40)SET 卷烟品牌 = (SELECT 卷烟品牌 FROM INSERTED)DECLARE 销售数量 MONEYSET 销售数量 = (SELECT 销售数量 FROM INSERTED)UPDATE 卷烟库存表SET 库存数量 = 库存数量 - 销售数量 , 库存金额 = ( 库存数量 - 销售数量 )*
11、 库存单价WHERE 卷烟品牌 = 卷烟品牌COMMIT TRANSACTIONGO - 请大家自行跟踪 卷烟库存表 和 卷烟销售表 的数据变化。- 针对 卷烟销售表 ,插入第一条测试数据,该数据是正常的。INSERT INTO 卷烟销售表 (卷烟品牌 ,购货商,销售数量 ,销售单价 ,销售金额 )SELECT 红塔山新势力 某, 购货商 ,10,12,1200GO- 针对 卷烟销售表 ,插入第二条测试数据, 该数据 销售金额 不等于 销售单价 * 销售数 量。- 触发器将自动更正数据,使 销售金额 等于 销售单价 * 销售数量。INSERT INTO 卷烟销售表 (卷烟品牌 ,购货商,销售数
12、量 ,销售单价 ,销售金额 )SELECT 红塔山人为峰 某, 购货商 ,10,22,2000GO卷烟库存表中找不到- 针对 卷烟销售表 ,插入第三条测试数据,该数据中的卷烟品牌在 对应。- 触发器将报错。INSERT INTO 卷烟销售表 (卷烟品牌 ,购货商 ,销售数量 ,销售单价 ,销售金额 )SELECT 红河 V8某, 购货商 ,10,60,600GO卷烟库存表中库存为- 针对 卷烟销售表 ,插入第三条测试数据,该数据中的卷烟品牌在 0。- 触发器将报错。INSERT INTO 卷烟销售表 (卷烟品牌 ,购货商 ,销售数量 ,销售单价 ,销售金额 )SELECT 玉溪 某, 购货商
13、,10,30,300GO- 查询数据SELECT * FROM卷烟库存表SELECT * FROM卷烟销售表GO- 修改触发器- 实质上,是将CREATE TRIGGER .修改为 ALTER TRIGGER . 即可。- 删除触发器DROP TRIGGER xxx GO- 删除测试环境DROP TABLE 卷烟库存表 GODROP TABLE 卷烟销售表GODROP TRIGGER T_INSERT_卷烟库存表GODROP TRIGGER T_INSERT_卷烟销售表GO# 触发器的基础知识和例子 : create trigger tr_name on table/viewfor | aft
14、er | instead of update,insert,deletewith encryptionas batch | if update (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 的区别After在触发事件发生以后才被激活 , 只可以建立在表上Instead of 代替了相应的触发事件而被执行 , 既可以
15、建立在表上也可以建立在视图上5 insert 、 update 、 delete :激活触发器的三种操作,可以同时执行,也可选其一6 if update (col_name):表明所作的操作对指定列是否有影响, 有影响, 则激活触发器。此外,因为 delete 操作只对行有影响,所以如果使用 delete 操作就不能用这条语句了 ( 虽然使用也不出错,但是不能激活触发器, 没意义 ) 。7 触发器执行时用到的两个特殊表: deleted ,inserteddeleted 和 inserted 可以说是一种特殊的临时表, 是在进行激活触发器时由系统自动生成 的,其结构与触发器作用的表结构是一 样
16、的,只是存放 的数据有差异。续 下面表格说明 deleted 与 inserted 数据的差异 deleted 与 inserted 数据的差异Inserted 存放进行 insert 和 update 操作后的数据Deleted 存放进行 delete 和 update 操作前的数据 注意: update 操作相当于先进行 delete 再进行 insert , 所以在进行 update 操作时,修 改前的数据拷贝一条到 deleted 表中,修改后 的数据在存到触发器作用的表的同时,也同时生成一条拷贝到 insered 表中/CREATE TRIGGER TRIGGER admixture
17、_receive_log ON dbo.chl_lydjFOR UPDATEASbegindeclare djsfxg char(10) declare wtbh char(20)select wtbh=wtbh from insertedupdate ly_tzk set djsfxg=已修改 where wtbh=wtbhendif (select data_sfjl from t_logsetup)= begin是declare oldlyrq datetimedeclare oldzl char (20)declare newcjmc char (100) declare newbzb
18、h char (60) declare newdj char (10)declare newlyrq datetimedeclare newzl char (20)declare oldcjmc char (100) d declare oldbzbh char (60) declare olddj char (10)declare xgr char (20) selectfromoldcjmc=cjmc,oldlyrq=lyrq,oldbzbh=bzbh,oldzl=zl,olddj=dj deletedselectnewcjmc=cjmc,newlyrq=lyrq,newbzbh=bzbh
19、,newzl=zl,newdj=djfrom insertedselect xgr=xgr from t_modifyuser where wtbh=wtbhif oldcjmcnewcjmcbegininsert into t_modifylog (wtbh, mod_time, mod_table, mod_field, ori_value, now_value, mod_people) values(wtbh,getdate(), chl_lydj,cjmc, oldcjmc, newcjmc, xgr)endend/ 修改时,直接把 create改为 alter即可/CREATE TR
20、IGGER TRIGGER ly_tzk_syf ON dbo.ly_tzkFOR insertASbegindeclare clmc char(100) declare dwbh char(100) declare syf char(100) declare dwgcbh char(100) declare wtbh char(50)declare dj_1 money declare feiyong_z money declare feiyong_xf money declare feiyong_sy moneydeclare dj char(20)select wtbh=wtbh , c
21、lmc=clmc , dwbh=dwbh ,syf=syf from insertedselect dj=dj from feihao_bz where clmc=clmcselect feiyong_z=feiyong_z, feiyong_sy=feiyong_sy from gongchengxinxi wherefeiyong_xf=feiyong_xf,dwgcbh=dwbhset dj_1=convert(money ,dj) if dj_1 0 begin set feiyong_xf=feiyong_xf+dj_1 set feiyong_sy=feiyong_sy-dj_1update ly_tzk setsyf=dj where wtbh=wtbhupdategongchengxinxiset feiyong_xf=feiyong_xf , feiyong_sy=feiyong_sy endelse update ly_tzk set syf=convert(char , 0.0) wherewhere dwgcbh=dwbhwtbh=wtbhend/CREATE TRIGG
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 2024-2030年中国沃尼妙林行业需求状况及竞争策略研究研究报告
- 2024-2030年中国汽车锂电池行业市场发展趋势与前景展望战略分析报告
- 2024-2030年中国汽车装饰靠垫行业市场深度调研及发展趋势与投资前景研究报告
- 2024-2030年中国汽车玻璃水行业竞争优势与前景趋势洞察研究报告
- 2024-2030年中国汽车板簧总成行业市场发展趋势与前景展望战略分析报告
- 2024-2030年中国汽油链锯行业市场发展趋势与前景展望战略分析报告
- 喷涂技术在医疗器械制造中的应用考核试卷
- 2024-2030年中国水泥设备行业市场发展现状及前景趋势与投资研究报告
- 2024-2030年中国水处理技术行业市场发展趋势与前景展望战略分析报告
- 2024-2030年中国氰基丙烯酸酯密封胶行业市场发展趋势与前景展望战略分析报告
- 小儿预防接种和其注意事项课件
- 2024-2030年肉牛行业市场发展分析与发展前景及投资战略研究报告
- 4.2 让家更美好(课件)2024-2025学年七年级道德与法治上册 (统编版2024)
- 自投户用光伏合同模板
- AQ 1071-2009 煤矿用非金属瓦斯输送管材安全技术要求(正式版)
- 2024年高考真题生物(江苏卷)
- 湖南省长沙市雅礼集团2023-2024学年八年级下学期期末考试物理试卷
- 苏教版四年级上册数学全册教案
- 食品安全管理师职业标准
- 统编小学语文六年级教材编排特点与教学建议1
- PPP项目运维期绩效评价工作方案
评论
0/150
提交评论