sqlserver_SQL触发器的使用及语法.docx

定义： 何为触发器？在SQL Server里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。常见的触发器有三种：分别应用于Insert , Update , Delete 事件。我为什么要使用触发器？比如，这么两个表：Create Table Student(-学生表StudentID int primary key,-学号.)Create Table BorrowRecord(-学生借书记录表BorrowRecordint identity(1,1),-流水号StudentIDint ,-学号BorrowDatedatetime,-借出时间ReturnDAteDatetime,-归还时间.)用到的功能有:1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号);2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。等等。这时候可以用到触发器。对于1，创建一个Update触发器：Create Trigger truStudentOn Student-在Student表中创建触发器for Update-为什么事件触发As-事件触发后所要做的事情if Update(StudentID)beginUpdate BorrowRecordSet StudentID=i.StudentIDFrom BorrowRecord br , Deletedd ,Inserted i-Deleted和Inserted临时表Where br.StudentID=d.StudentIDend理解触发器里面的两个临时的表：Deleted , Inserted 。注意Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息，分别是：虚拟表Inserted虚拟表Deleted在表记录新增时存放新增的记录不存储记录修改时存放用来更新的新记录存放更新前的记录删除时不存储记录存放被删除的记录一个Update 的过程可以看作为：生成新的记录到Inserted表，复制旧的记录到Deleted表，然后删除Student记录并写入新纪录。对于2，创建一个Delete触发器Create trigger trdStudentOn Studentfor DeleteAsDelete BorrowRecordFrom BorrowRecord br , Delted dWhere br.StudentID=d.StudentID从这两个例子我们可以看到了触发器的关键：A.2个临时的表；B.触发机制。SQL触发器实例2USE MasterGOIF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = U AND NAME = 卷烟库存表)DROP TABLE 卷烟库存表GOIF EXISTS (SELECT NAME FROM 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) PRIMARY KEY NOT NULL,库存数量 INT NULL,库存单价 MONEY NULL,库存金额 MONEY NULL)GO-创建触发器，示例1IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = TR AND NAME = T_INSERT_卷烟库存表)DROP TRIGGER T_INSERT_卷烟库存表GOCREATE 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 ALLSELECT 云南映像,100,60,500 UNION ALLSELECT 玉溪,0,30,0GO-查询数据SELECT * FROM 卷烟库存表GO-触发器示例2IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = TR AND NAME = T_INSERT_卷烟销售表)DROP TRIGGER T_INSERT_卷烟销售表GOCREATE TRIGGER T_INSERT_卷烟销售表ON 卷烟销售表FOR INSERTASBEGIN TRANSACTION-检查数据的合法性：销售的卷烟是否有库存，或者库存是否大于零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)-回滚事务ROLLBACKRETURNEND-对合法的数据进行处理-强制执行下列语句，保证业务规则UPDATE 卷烟销售表SET 销售金额 = 销售数量 * 销售单价WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED)DECLARE 卷烟品牌 VARCHAR(40)SET 卷烟品牌 = (SELECT 卷烟品牌 FROM INSERTED)DECLARE 销售数量 MONEYSET 销售数量 = (SELECT 销售数量 FROM INSERTED)UPDATE 卷烟库存表SET 库存数量 = 库存数量 - 销售数量,库存金额 = (库存数量 - 销售数量)*库存单价WHERE 卷烟品牌 = 卷烟品牌COMMIT TRANSACTIONGO-请大家自行跟踪卷烟库存表和卷烟销售表的数据变化。-针对卷烟销售表，插入第一条测试数据，该数据是正常的。INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)SELECT 红塔山新势力,某购货商,10,12,1200GO-针对卷烟销售表，插入第二条测试数据，该数据 销售金额 不等于 销售单价 * 销售数量。-触发器将自动更正数据，使 销售金额 等于 销售单价 * 销售数量。INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)SELECT 红塔山人为峰,某购货商,10,22,2000GO-针对卷烟销售表，插入第三条测试数据，该数据中的卷烟品牌在 卷烟库存表中找不到对应。-触发器将报错。INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)SELECT 红河V8,某购货商,10,60,600GO-针对卷烟销售表，插入第三条测试数据，该数据中的卷烟品牌在 卷烟库存表中库存为0。-触发器将报错。INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)SELECT 玉溪,某购货商,10,30,300GO-查询数据SELECT * FROM 卷烟库存表SELECT * FROM 卷烟销售表GO-修改触发器-实质上，是将 CREATE TRIGGER . 修改为 ALTER TRIGGER .即可。-删除触发器DROP TRIGGER xxxGO-删除测试环境DROP TABLE 卷烟库存表GODROP TABLE 卷烟销售表GODROP TRIGGER T_INSERT_卷烟库存表GODROP TRIGGER T_INSERT_卷烟销售表GO#触发器的基础知识和例子：create trigger tr_nameon table/viewfor | after | 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代替了相应的触发事件而被执行,既可以建立在表上也可以建立在视图上5 insert、update、delete：激活触发器的三种操作，可以同时执行，也可选其一6 if update (col_name)：表明所作的操作对指定列是否有影响，有影响，则激活触发器。此外，因为delete 操作只对行有影响，所以如果使用delete操作就不能用这条语句了(虽然使用也不出错，但是不能激活触发器，没意义)。7 触发器执行时用到的两个特殊表：deleted ,inserteddeleted 和inserted 可以说是一种特殊的临时表，是在进行激活触发器时由系统自动生成的，其结构与触发器作用的表结构是一样的，只是存放 的数据有差异。续下面表格说明deleted 与inserted 数据的差异deleted 与inserted 数据的差异Inserted存放进行insert和update 操作后的数据Deleted存放进行delete 和update操作前的数据注意：update 操作相当于先进行delete 再进行insert ,所以在进行update操作时，修改前的数据拷贝一条到deleted 表中，修改后的数据在存到触发器作用的表的同时，也同时生成一条拷贝到insered表中/CREATE TRIGGER TRIGGER admixture_receive_log ON dbo.chl_lydjFOR UPDATEASbegindeclare djsfxg char(10)declare wtbh char(20)select wtbh=wtbh from insertedupdate ly_tzk set djsfxg=已修改 wherewtbh=wtbhendif (select data_sfjl from t_logsetup)=是begindeclare oldcjmc char (100)declare oldlyrq datetimedeclare oldbzbh char (60)declare oldzl char (20)declare olddj char (10)declare newcjmc char (100)declare newlyrq datetimedeclare newbzbh char (60)declare newzl char (20)declare newdj char (10)declare xgr char (20)select oldcjmc=cjmc,oldlyrq=lyrq,oldbzbh=bzbh,oldzl=zl,olddj=dj from deletedselect newcjmc=cjmc,newlyrq=lyrq,newbzbh=bzbh,newzl=zl,newdj=dj from 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 TRIGGER 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 moneydeclare feiyong_z moneydeclare feiyong_xf moneydeclare feiyong_sy moneydeclare dj char(20)select wtbh=wtbh , clmc=clmc , dwbh=dwbh ,syf=syf from insertedselectdj=dj from feihao_bz whereclmc=clmcselect feiyong_z=feiyong_z, feiyong_xf=feiyong_xf, feiyong_sy=feiyong_sy from gongchengxinxi wheredwgcbh=dwbhset dj_1=convert(money ,dj)if dj_1 0beginset feiyong_xf=feiyong_xf+dj_1set feiyong_sy=feiyong_sy-dj_1update ly_tzk setsyf=djwherewtbh=wtbhupdate gongchengxinxi setfeiyong_xf=feiyong_xf,feiyong_sy=feiyong_sywheredwgcbh=dwbhendelseupdate ly_tzk set syf=convert(char , 0.0) wherewtbh=wtbhend/CREATE TRIGGER TRIGGER l