sqlserver触发器.docx

sqlserver触发器.docx

《sqlserver触发器.docx》由会员分享，可在线阅读，更多相关《sqlserver触发器.docx（18页珍藏版）》请在冰点文库上搜索。

sqlserver触发器

初学sqlserver2008之触发器

　　触发器（trigger）：

是一种特殊的存储过程，可以用来对表实施复杂的完整性约束，保持数据的一致性。

当触发器所保护的数据发生改变时，触发器会自动被激活，并执行触发器中所定义的相关操作，从而保证对数据的不完整性约束或不正确的修改。

　　在SQLSERVER2008中，有三种类型的触发器：

（1）DML触发器：

是指触发器在数据库中发生数据操作语言（DML）事件时将启用。

DML事件即指在表或视图中修改数据的insert、update、delete语句也。

（2）DDL触发器：

是指当服务器或数据库中发生数据定义语言（DDL）事件时将启用。

DDL事件即指在表或索引中的create、alter、drop语句也。

（3）登陆触发器：

是指当用户登录SQLSERVER实例建立会话时触发。

　　不过根据DML触发器触发的方式不同又分为以下两种情况：

（1）AFTER触发器：

它是在执行INSERT、UPDATE、DELETE语句操作之后执行触发器操作。

它主要是用于记录变更后的处理或检查，一旦发生错误，可以用RollbackTransaction语句来回滚本次扣件，不过不能对视图定义AFTER触发器。

（2）INSTEADOF触发器：

它在执行INSERT、UPDATE、DELETE语句操作之前执行触发器本身所定义的操作。

而INSTEADOF触发器是可以定义在视图上的。

 　　在SQLSERVER2008中，DML触发器的实现使用两个逻辑表DELETED和INSERTED。

这两个表是建立在数据库服务器的内存中，由系统管理的逻辑表，我们对于它只有只读的权限。

DELETED和INSERED表的结构和触发器所在的数据表的结构是一样的。

当触发器执行完成后，它们也就会被自动删除。

INSERED表用于存放你在操件insert、update、delete语句后，更新的记录。

比如你插入一条数据，那么就会把这条记录插入到INSERTED表。

DELETED表用于存放你在操作 insert、update、delete语句前，你创建触发器表中数据库。

比如你原来的表中有三条数据，那么他也有三条数据。

下面我们就开始创建触发器吧!

触发器定义的格式我就不打一遍，你打开帮助里有的事。

我们先创建一张如下的表吧！

然后以它为例吧。

createtabletable1

（

user_idintprimarykeyidentity（1,1）,

user_namevarchar（3）notnull

）

下面就是创建一个简单的after触发器：

createtriggertrigMessageList

ontable1

afterinsert,update

asraiserror（'数据一致性验证',16,1）

此时如果我们手动打开table1，输入一条，根本就插入不了，我们只能写一条如下的SQL语句插入一条数据

insertintotable1（user_name）values（'23'）

结果数据是插入成功了，但是会出显如下图所示的提示：

下面我再创建一个稍微有点复杂的after触发器：

--该触发器作用是：

如果向table1中插入数据时，

--先检查要向table1插入的这条的USER_ID是否USERS（我创建的另一个表）表中的USER_ID字段中有，如果不存在则不允许插入

createtriggertrigInsertedMessages

ontable1

afterinsert

as

ifexists（select*frominsertedawherea.user_idnotin（selectUSER_IDfromUSERS））

begin

raiserror（'数据一致性验证',16,1）

rollbacktransaction

end

insertedof触发器和after触发器写法差不多，在此就不多写了。

下面创建一个简单的DDL触发器：

--它的作用是：

防止数据库Test（我创建的一个实验数据库）中的任一表被修改或删除

createtriggertrigDB

ondatabase

fordrop_table,alter_table

as

print'你一定要禁用触发器“trigDB"才能删除或修改这个数据库的表'

rollback

删除触发器：

droptriggertrigDB

ONDATABASE

一定要记得加上ONDATABASE这条，否则就删除不了。

最后再来条开启和禁用触发器的语句：

disabletriggertrigDBondatabase--禁用触发器

enabletriggertrigDBondatabase--开启触发器

一﹕触发器是一种特殊的存储过程﹐它不能被显式地调用﹐而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。

所以触发器可以用来实现对表实施复杂的完整性约`束。

　　二﹕SQLServer为每个触发器都创建了两个专用表﹕Inserted表和Deleted表。

这两个表由系统来维护﹐它们存在于内存中而不是在数据库中。

这两个表的结构总是与被该触发器作用的表的结构相同。

触发器执行完成后﹐与该触发器相关的这两个表也被删除。

Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。

Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。

　　三﹕Insteadof和After触发器

SQLServer2000提供了两种触发器﹕Insteadof和After触发器。

这两种触发器的差别在于他们被激活的同﹕

Insteadof触发器用于替代引起触发器执行的T-SQL语句。

除表之外﹐Insteadof触发器也可以用于视图﹐用来扩展视图可以支持的更新操作。

After触发器在一个Insert,Update或Deleted语句之后执行﹐进行约束检查等动作都在After触发器被激活之前发生。

After触发器只能用于表。

一个表或视图的每一个修改动作（insert,update和delete）都可以有一个insteadof触发器﹐一个表的每个修改动作都可以有多个After触发器。

　　四﹕触发器的执行过程如果一个Insert﹑update或者delete语句违反了约束﹐那幺After触发器不会执行﹐因为对约束的检查是在After触发器被激动之前发生的。

所以After触发器不能超越约束。

Insteadof触发器可以取代激发它的操作来执行。

它在Inserted表和Deleted表刚刚建立﹐其它任何操作还没有发生时被执行。

因为Insteadof触发器在约束之前执行﹐所以它可以对约束进行一些预处理。

　　五﹕使用T-SQL语句来创建触发器 基本语句如下﹕                 

createtriggertrigger_name          

on{table_name|view_name}          

{for|After|Insteadof}          [insert,update,delete]          

as          sql_statement 

　　六﹕删除触发器：

 基本语句如下﹕

    droptriggertrigger_name 

　　七：

查看数据库中已有触发器：

--查看数据库已有触发器

usejxcSoftwaregoselect*fromsysobjectswherextype='TR' 

--查看单个触发器execsp_helptext'触发器名'

　　八﹕修改触发器：

基本语句如下﹕altertriggertrigger_name          

on{table_name|view_name}          

{for|After|Insteadof}          [insert,update,delete]          

as          sql_statement

1、触发器实例

CreateTableStudent（             --学生表       

　　　　　　StudentIDintprimarykey,      --学号       

　　　　　　....      ）

     CreateTableBorrowRecord（              --学生借书记录表       

　　　　　　BorrowRecord  intidentity（1,1）,      --流水号         

　　　　　　 StudentID     int,                   --学号       

　　　　　　BorrowDate    datetime,               --借出时间       

　　　　　　ReturnDAte    Datetime,               --归还时间       

　　　　　　　　　　　　...     ）

 用到的功能有:

1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关（也就是同时更改借书记录表的学号）;       

　　　　　　　2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。

    等等。

    这时候可以用到触发器。

对于1，创建一个Update触发器：

    CreateTriggertruStudent      

　　OnStudent                        --在Student表中创建触发器      

　　forUpdate                         --为什么事件触发    

　　As                                      --事件触发后所要做的事情      

　  ifUpdate（StudentID）                 

　　begin

     UpdateBorrowRecord          

　　SetStudentID=i.StudentID          

　　FromBorrowRecordbr,Deleted  d,Insertedi     --Deleted和Inserted临时表          

　　Wherebr.StudentID=d.StudentID

      end                           

理解触发器里面的两个临时的表：

Deleted,Inserted。

注意Deleted与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。

一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息，分别是：

　　　　　　　　　　虚拟表Inserted                    虚拟表Deleted

在表记录新增时      存放新增的记录                        不存储记录        

修改时          　　存放用来更新的新记录                  存放更新前的记录        

删除时          　　不存储记录                            存放被删除的记录

    一个Update的过程可以看作为：

生成新的记录到Inserted表，复制旧的记录到Deleted表，然后删除Student记录并写入新纪录。

    对于2，创建一个Delete触发器    

　　CreatetriggertrdStudent      

　　OnStudent      

　  forDelete    

　　As      

　　DeleteBorrowRecord        

　　FromBorrowRecordbr,Deltedd        

　　Wherebr.StudentID=d.StudentID

    从这两个例子我们可以看到了触发器的关键：

A.2个临时的表；B.触发机制。

SQL触发器实例2

USEMaster

GO

IFEXISTS（SELECTNAMEFROMSYSOBJECTSWHEREXTYPE='U'ANDNAME='卷烟库存表'）

DROPTABLE卷烟库存表

GO

IFEXISTS（SELECTNAMEFROMSYSOBJECTSWHEREXTYPE='U'ANDNAME='卷烟销售表'）

DROPTABLE卷烟销售表

GO

--业务规则：

销售金额=销售数量*销售单价业务规则。

CREATETABLE卷烟销售表

（卷烟品牌VARCHAR（40）PRIMARYKEYNOTNULL,

 购货商VARCHAR（40）NULL,

 销售数量INTNULL,

 销售单价MONEYNULL,

 销售金额MONEYNULL

）

GO

--业务规则：

库存金额=库存数量*库存单价业务规则。

CREATETABLE卷烟库存表

（卷烟品牌VARCHAR（40）PRIMARYKEYNOTNULL,

 库存数量INTNULL,

 库存单价MONEYNULL,

 库存金额MONEYNULL

）

GO

--创建触发器，示例1

/*创建触发器[T_INSERT_卷烟库存表]，这个触发器较简单。

说明：

每当[卷烟库存表]发生INSERT动作，则引发该触发器。

触发器功能：

强制执行业务规则，保证插入的数据中，库存金额=库存数量*库存单价。

注意：

[INSERTED]、[DELETED]为系统表，不可创建、修改、删除，但可以调用。

重要：

这两个系统表的结构同插入数据的表的结构。

*/

IFEXISTS（SELECTNAMEFROMSYSOBJECTSWHEREXTYPE='TR'ANDNAME='T_INSERT_卷烟库存表'）

DROPTRIGGERT_INSERT_卷烟库存表

GO

CREATETRIGGERT_INSERT_卷烟库存表

ON卷烟库存表

FORINSERT

AS

--提交事务处理

BEGINTRANSACTION

--强制执行下列语句，保证业务规则

UPDATE卷烟库存表

SET库存金额=库存数量*库存单价

WHERE卷烟品牌IN（SELECT卷烟品牌fromINSERTED）

COMMITTRANSACTION

GO

/*针对[卷烟库存表]，插入测试数据：

注意，第一条数据（红塔山新势力）中的数据符合业务规则，

第二条数据（红塔山人为峰）中，[库存金额]空，不符合业务规则，

第三条数据（云南映像）中，[库存金额]不等于[库存数量]乘以[库存单价]，不符合业务规则。

第四条数据库存数量为0。

请注意在插入数据后，检查[卷烟库存表]中的数据是否库存金额=库存数量*库存单价。

*/

INSERTINTO卷烟库存表（卷烟品牌,库存数量,库存单价,库存金额）

SELECT'红塔山新势力',100,12,1200UNIONALL

SELECT'红塔山人为峰',100,22,NULLUNIONALL

SELECT'云南映像',100,60,500UNIONALL

SELECT'玉溪',0,30,0GO

--查询数据

SELECT*FROM卷烟库存表

GO

/*

结果集

RecordId卷烟品牌库存数量库存单价库存金额

--------------------------------------------

1红塔山新势力10012.00001200.0000

2红塔山人为峰10022.00002200.0000

3云南映像10060.00006000.0000

4玉溪030.0000.0000

（所影响的行数为4行）

*/

--触发器示例2

/*创建触发器[T_INSERT_卷烟销售表]，该触发器较复杂。

说明:

每当[卷烟库存表]发生INSERT动作，则引发该触发器。

触发器功能：

实现业务规则。

业务规则:

如果销售的卷烟品牌不存在库存或者库存为零，则返回错误。

否则则自动减少[卷烟库存表]中对应品牌卷烟的库存数量和库存金额。

*/

IFEXISTS（SELECTNAMEFROMSYSOBJECTSWHEREXTYPE='TR'ANDNAME='T_INSERT_卷烟销售表'）

DROPTRIGGERT_INSERT_卷烟销售表

GO

CREATETRIGGERT_INSERT_卷烟销售表

ON卷烟销售表

FORINSERT

AS

BEGINTRANSACTION

--检查数据的合法性：

销售的卷烟是否有库存，或者库存是否大于零

IFNOTEXISTS（

SELECT库存数量

FROM卷烟库存表

WHERE卷烟品牌IN（SELECT卷烟品牌FROMINSERTED））

BEGIN

--返回错误提示RAISERROR（'错误！

该卷烟不存在库存，不能销售。

',16,1）

--回滚事务

ROLLBACK

RETURN

END

IFEXISTS（SELECT库存数量FROM卷烟库存表WHERE卷烟品牌IN（SELECT卷烟品牌FROMINSERTED）AND库存数量<=0）BEGIN--返回错误提示RAISERROR（'错误！

该卷烟库存小于等于0，不能销售。

',16,1）--回滚事务ROLLBACKRETURNEND

--对合法的数据进行处理

--强制执行下列语句，保证业务规则

UPDATE卷烟销售表

SET销售金额=销售数量*销售单价

WHERE卷烟品牌

IN（SELECT卷烟品牌FROMINSERTED）

DECLARE@卷烟品牌VARCHAR（40）

SET@卷烟品牌=（SELECT卷烟品牌FROMINSERTED）

DECLARE@销售数量MONEY

SET@销售数量=（SELECT销售数量FROMINSERTED）

UPDATE卷烟库存表

SET库存数量=库存数量-@销售数量,库存金额=（库存数量-@销售数量）*库存单价

WHERE卷烟品牌=@卷烟品牌

COMMITTRANSACTION

GO

--请大家自行跟踪[卷烟库存表]和[卷烟销售表]的数据变化。

--针对[卷烟销售表]，插入第一条测试数据，该数据是正常的。

INSERTINTO卷烟销售表（卷烟品牌,购货商,销售数量,销售单价,销售金额）

SELECT'红塔山新势力','某购货商',10,12,1200

GO

--针对[卷烟销售表]，插入第二条测试数据，该数据销售金额不等于销售单价*销售数量。

--触发器将自动更正数据，使销售金额等于销售单价*销售数量。

INSERTINTO卷烟销售表（卷烟品牌,购货商,销售数量,销售单价,销售金额）

SELECT'红塔山人为峰','某购货商',10,22,2000

GO

--针对[卷烟销售表]，插入第三条测试数据，该数据中的卷烟品牌在卷烟库存表中找不到对应。

--触发器将报错。

INSERTINTO卷烟销售表（卷烟品牌,购货商,销售数量,销售单价,销售金额）

SELECT'红河V8','某购货商',10,60,600

GO

/*消息50000，级别16，状态1，过程T_INSERT_卷烟销售表，第15行错误！

该卷烟不存在库存，不能销售。

消息3609，级别16，状态1，第1行事务在触发器中结束。

批处理已中止。

*/

--针对[卷烟销售表]，插入第三条测试数据，该数据中的卷烟品牌在卷烟库存表中库存为0。

--触发器将报错。

INSERTINTO卷烟销售表（卷烟品牌,购货商,销售数量,销售单价,销售金额）

SELECT'玉溪','某购货商',10,30,300

GO

/*消息50000，级别16，状态1，过程T_INSERT_卷烟销售表，第29行错误！

该卷烟库存小于等于0，不能销售。

消息3609，级别16，状态1，第1行事务在触发器中结束。

批处理已中止。

*/

--查询数据

SELECT*FROM卷烟库存表

SELECT*FROM卷烟销售表

GO

补充：

1、本示例主要通过一个简单的业务规则实现来进行触发器使用的说明，具体的要根据需要灵活处理； 

2、关于触发器要理解并运用好INSERTED，DELETED两个系统表； 

3、本示例创建的触发器都是FORINSERT,具体的语法可参考：

Trigger语法 

CREATETRIGGERtrigger_name 

ON{table|view} 

[WITHENCRYPTION]--用于加密触发器 

{ 

{{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE] } 

[WITHAPPEND] 

[NOTFORREPLICATION] 

AS 

[{IFUPDATE（ column） 

[{AND|OR}UPDATE（column）] 

[...n] 

|IF（ COLUMNS_UPDATED（）{bitwise_operator}updated_bitmask） 

{ comparison_operator}column_bitmask[...n] 

}] 

sql_statement[...n] 

} 

} 

4、关于触发器，还应该注意 

（1）、DELETE触发器不能捕获TRUNCATETABLE语句。

（2）、触发器中不允许以下Transact-SQL语句：

ALTERDATABASECREATEDATABASEDISKINIT 

DISKRESIZEDROPDATAB