SQLServer触发器学习笔记

1、学习好资料欢迎下载触发器一:触发器是一种特殊的存储过程，它不能被显式地调用，而是在往表中插入记录、更新 记录或者删除记录时被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约束。二: SQL Server为每个触发器都创建了两个专用表：Inserted表和Deleted表。这两个表由系统来维护，它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行完成后，与该触发器相关的这两个表也被删除。Deleted表存放由于执行 Delete或Update语句而要从表中删除的所有行。Inserted表存放由于执行Insert或Update语句而要向表中插入的所

2、有行。三：In stead of和After触发器SQL Server提供了两种触发器：In stead of和After触发器。这两种触发器的差别在于他们被激活的操作：In stead of触发器用于替代引起触发器执行的T-SQL语句。除表之外，In stead of触发器也可以用于视图，用来扩展视图可以支持的更新操作。After触发器在一个Insert,Update或Deleted语句之后执行，进行约束检查等动作都在After触发器被激活之前发生。After触发器只能用于表。一个表或视图的每一个修改动作(in sert,update和delete)都可以有一个in stead of触发器，

3、一个表的每个修改动作都可以有多 个After触发器。INSTEAD OF触发器被用于更新那些没有办法通过正常方式更新的视图。通常不能在一个基于连接的视图上进行 DELETE操作，可以编写一个INSTEAD OF DELETE 触发器来实现 删除。可以访问那些如果视图是一个真正的表时已经被删除的数据行，它将把删除的行存储在deleted临时表中。本次练习将通过具体的例子介绍如何使用INSTEAD OF触发器，使读者掌握INSTEAD OF触发器的使用方法。向student表中插入数据时，检查学号是否存在于 student表中，如存在则进行插入操作，否则 就不插入，具体语句如下所示：CREATE

4、TRIGGER checkidON dbo.stude ntINSTEAD OF in sertASIF NOT EXISTS(SELECT * FROM dbo.stude ntWHERE ID=(SELECT ID FROM INSERTED)BEGINROLLBACK TRANSACTIONPRINT 要处理记录的学号不存在！ENDELSEBEGININSERT NTO dbo.stude ntselect * from in sertedPRINT 已经成功处理记录!END执行以上代码后，然后，输入以下代码进行测试，代码如下：INSERT INTO dbo.stude ntVALUES

5、史琳达,女,20,006)四：触发器的执行过程 如果一个Insert、update或者delete语句违反了约束，那幺 After触 发器不会执行，因为对约束的检查是在After触发器被激动之前发生的。所以 After触发器不能超越约束。In stead of触发器可以取代激发它的操作来执行。它在In serted表和Deleted表刚刚建立，其它任何操作还没有发生时被执行。因为In stead of触发器在约束之前执行，所以它可以对约束进行一些预处理。五：使用T-SQL语句来创建触发器基本语句如下：create trigger trigger, nameon table_ name | vi

6、ew_ namefor | After | In stead of in sert, update,delete as sql_statement六：删除触发器：基本语句如下：drop trigger trigger, name七：查看数据库中已有触发器：-查看数据库已有触发器use jxcSoftware go select * from sysobjects where xtype=TR-查看单个触发器 exec sp_helptext 触发器名八：修改触发器：基本语句如下：alter trigger trigger_ nameon table_ name | view_ namefor

7、| After | In stead of in sert, update,delete as sql_stateme nt 1触发器实例-学号-学生借书记录表-流水号-学号-借出时间-归还时间Create Table Stude nt(-学生表 Stude ntID int primary key,Create Table BorrowRecord( BorrowRecord int ide ntity(1,1), Stude ntID i nt ,BorrowDate datetime,ReturnDAte Datetime,用到的功能有：1如果我更改了学生的学号，我希望他的借书记录仍然与

8、这个学生相关(也就是同时更改借书记录表的学号);2如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。这时候可以用到触发器。对于1,创建一个Update触发器：Create Trigger truStude ntOn Student-在Student表中创建触发器for Update-为什么事件触发As-事件触发后所要做的事情if Update(Stude ntID)beginUpdate BorrowRecordSet Stude ntlD=i.Stude ntIDFrom BorrowRecord br ,Deleted d ,Inserted i -Deleted 和 In

9、serted 临时表Where br.Stude ntlD=d.Stude ntIDend理解触发器里面的两个临时的表：Deleted , Inserted。注意Deleted与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息，分别是：虚拟表Inserted虚拟 Deleted在寰记录新增时存放新增的记录不存储记最修改时存放用来更紊的弱记录存放更新前的记录删除时不存储记录存放被删除的记录一个Update的过程可以看作为：生成新的记录到Inserted表，复制旧的记录到Deleted表,然后删除Student记录并写

10、入新纪录。对于2,创建一个Delete触发器Create trigger trdStude ntOn Stude ntfor DeleteAsDelete BorrowRecordFrom BorrowRecord br , Delted dWhere br.Stude ntlD=d.Stude ntIDSQL触发器实例2USE MasterGOIF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = U AND NAME = 卷烟 库存表)DROP TABLE卷烟库存表GOIF EXISTS (SELECT NAME FROM SYSOBJEC

11、TS 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 NUL

12、L,库存单价 MONEY NULL,库存金额 MONEY NULL)GO-创建触发器，示例1/*创建触发器T_INSERT_卷烟库存表，这个触发器较简单。说明： 每当卷烟库存表发生INSERT动作，则引发该触发器。触发器功能：强制执行业务规则， 保证插入的数据中，库存金额=库存数量*库存单价。注意：INSERTED、DELETED为系统表，不可创建、修改、删除，但可以调用。重要：这两个系统表的结构同插入数据的表的结构。*/IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = TR AND NAME =T_INSERT_卷烟库存表)DROP T

13、RIGGER T_INSERT_ 卷烟库存表GOCREATE TRIGGER T_INSERT_ 卷烟库存表ON卷烟库存表FOR INSERTAS-提交事务处理BEGIN TRANSACTION-强制执行下列语句，保证业务规则UPDATE卷烟库存表SET库存金额=库存数量*库存单价WHERE 卷烟品牌 IN (SELECT 卷烟品牌 from INSERTED)COMMIT TRANSACTIONGO/*针对卷烟库存表，插入测试数据：注意，第一条数据（红塔山新势力）中的数据符合业务规则，第二条数据（红塔山人为峰）中，库存金额空，不符合业务规则，第三条数据（云南映像）中，库存金额不等于库存数量乘

14、以库存单价,不符合业务规则。 第四条数据库存数量为 0。请注意在插入数据后，检查卷烟库存表中的数据是否 库存金额=库存数量*库存单价。*/INSERT INTO 卷烟库存表（卷烟品牌，库存数量，库存单价，库存金额）SELECT红塔山新势力,100,12,1200 UNION ALLSELECT 红塔山人为峰,100,22,NULL UNION ALLSELECT 云南映像,100,60,500 UNION ALLSELECT 玉溪,0,30,0 GO-查询数据SELECT * FROM 卷烟库存表GO/*结果集Recordld卷烟品牌库存数量库存单价库存金额1 红塔山新势力 100 12.00

15、00 1200.00002 红塔山人为峰 100 22.0000 2200.00003 云南映像 100 60.0000 6000.00004 玉溪 0 30.0000 .0000（所影响的行数为4行）*/-触发器示例2/*创建触发器T_INSERT_卷烟销售表，该触发器较复杂。说明：每当卷烟库存表发生INSERT动作，则引发该触发器。 触发器功能：实现业务规则。业务规则：如果销售的卷烟品牌不存在库存或者库存为零，则返回错误。否则则自动减少卷烟库存表中对应品牌卷烟的库存数量和库存金额。*/IF EXISTS （SELECT NAME FROM SYSOBJECTS WHERE XTYPE =

16、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

17、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 卷

18、烟品牌 FROM INSERTED）DECLARE 销售数量 MONEYSET 销售数量 =（SELECT 销售数量 FROM INSERTED）UPDATE卷烟库存表SET库存数量=库存数量-销售数量，库存金额=（库存数量-销售数量广库存单价 WHERE卷烟品牌=卷烟品牌COMMIT TRANSACTIONGO-请大家自行跟踪卷烟库存表和卷烟销售表的数据变化。-针对卷烟销售表，插入第一条测试数据，该数据是正常的。INSERT INTO 卷烟销售表（卷烟品牌，购货商,销售数量，销售单价，销售金额）SELECT 红塔山新势力,某购货商,10,12,1200GO-针对卷烟销售表，插入第二条测试数据

19、，该数据销售金额不等于销售单价*销售数量。-触发器将自动更正数据，使销售金额 等于 销售单价*销售数量。INSERT INTO 卷烟销售表（卷烟品牌，购货商,销售数量，销售单价，销售金额）SELECT 红塔山人为峰,某购货商,10,22,2000GO-针对卷烟销售表,插入第三条测试数据，该数据中的卷烟品牌在卷烟库存表中找不到对应。-触发器将报错。INSERT INTO 卷烟销售表（卷烟品牌，购货商,销售数量，销售单价，销售金额）SELECT 红河 V8,某购货商,10,60,600GO/*消息50000，级别16,状态1,过程T_INSERT_卷烟销售表，第 15行 错误！该卷烟不存在库存，不

20、能销售。消息3609,级别16,状态1，第1行 事务在触发器中结束。批处理已中止。*/-针对卷烟销售表，插入第三条测试数据， 该数据中的卷烟品牌在卷烟库存表中库存为0。-触发器将报错。INSERT INTO 卷烟销售表(卷烟品牌，购货商,销售数量，销售单价，销售金额)SELECT 玉溪,某购货商,10,30,300GO/*消息50000，级别16，状态1,过程T_INSERT_卷烟销售表，第 29行错误！该卷烟 库存小于等于0，不能销售。 消息3609，级别16,状态1,第1行事务在触发器中结 束。批处理已中止。*/-查询数据SELECT * FROM 卷烟库存表SELECT * FROM 卷

21、烟销售表GO补充：1、本示例主要通过一个简单的业务规则实现来进行触发器使用的说明，具体的要根据需要 灵活处理；2、 关于触发器要理解并运用好INSERTED , DELETED两个系统表；3、 本示例创建的触发器都是FOR INSERT ,具体的语法可参考：Trigger 语法CREATE TRIGGER trigger, nameON table | view WITH ENCRYPTION -用于加密触发器 FOR | AFTER | INSTEAD OF INSERT , UPDA TE WITH APPEND NOT FOR REPLICATION AS IF UPDATE (colu

22、 mn ) AND | OR UPDATE ( column ).n | IF ( COLUMNS_UPDA TED ( ) bitwise_operator updated_bitmask ) comparis on _operator colu mn _bitmask . n sql_stateme nt n 4、关于触发器，还应该注意(1)、DELETE 触发器不能捕获 TRUNCA TE TABLE 语句。、触发器中不允许以下Transact-SQL语句：ALTER DATABASE CREA TE DATABASE DISK INITDISK RESIZE DROP DATABASE

23、 LOAD DATABASELOAD LOG RECONFIGURE RESTORE DATABASERESTORE LOG(3)、触发器最多可以嵌套32层。*/-修改触发器-实质上，是将 CREATE TRIGGER . 修改为 ALTER TRIGGER .即可。-删除触发器DROP TRIGGER xxxGO-删除测试环境DROP TABLE卷烟库存表GODROP TABLE卷烟销售表GODROP TRIGGER T_INSERT_ 卷烟库存表GODROP TRIGGERT_INSERT_ 卷烟销售表GOIf If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If If IfII II II II II II II II II II II II II II II II II II I