sqlserverSQL触发器的使用及语法

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