SQL Sever触发器

SQL Sever触发器

一、触发器概述

1、触发器是一种特殊类型的存储过程，当表中数据被更新时，SQLServer自动执行触发器。

2、使用触发器可以实施更为复杂的数据完整性约束。

在同一数据库中的简单参照完整性常通过PRIMARY  KEY约束和FOREIGN  KEY约束来实现。但参照完整性约束不能参照其他数据库中的对象； 而触发器可跨数据库的参照完整性约束只能通过触发器来实现。

CHECK约束只能引用当前列（列级CHECK约束）或当前表（表级CHECK约束）中的列值，而触发器则可引用其他表中的列值。

    3、CREATE TRIGGER语句建立触发器，它说明一个触发器的触发表。触发操作事件名称和触发器所执行的操作。触发器是一种特殊的存储过程，它不允许带参数，也不能被直接调用，而只能由系统自动触发执行。

    SQL  Server提供以下两种触发方式：

    ·后触发：在触发操作（INSERT、 UPDATE或DELETE）执行完成，并处理过所有约束后激活触发器，这种方式称为后触发。如果触发操作违反约束条件，将导致事务回滚，这时就不会执行后触发器。但在视图上不能采用后触发方式定义触发器。

 ·替代触发：当触发操作发生时，不是执行的触发的SQL语句，从而替代触发语句的操作。在表和视图中，每个INSERT、UPDATE或DELETE最多可以定义一个INSTEAD OF触发器。

数据库引擎首先创建临时inserted表和deleted表，之后，SQL  Server停止执行通常的操作，而转去执行替代触发器。

二、建立触发器

在企业管理其使用CREATE TRIGGER语句创建触发器，CREATE TRIGGER格式为：

CREATETRIGGER trigger_name

ON{ table | view }

[WITH ENCRYPTION ]

{

    { { FOR | AFTER | INSTEAD OF } { [ INSERT ][ , ] [ UPDATE ] }

        [ WITH APPEND ]

        [ NOT FOR REPLICATION ]

        AS

        [ { IF UPDATE ( column )

            [ { AND | OR } UPDATE ( column ) ]

                [ ...n ]

        |IF ( COLUMNS_UPDATED ( ) {bitwise_operator } updated_bitmask )

                { comparison_operator }column_bitmask [ ...n ]

        } ]

        sql_statement [ ...n ]

    }

}

    在CREATE  TRIGGER语句中，trigger－name为所建立的触发器名称。它必须遵守SQL  Server标识符命名规则，并且在一个数据库中，触发器名称必须保持唯一。

    table为引发触发器的表名称，这些表内的数据更新操作将激活触发器。所以，又将这些表称作触发器表。

    SQL Server将触发器的定义文本存储在syscomments系统表中，使用WITH  ENCRYPTION选项要求SQL  Server对触发器定义文本进行加密存储，这样能够防止他人从syscomments系统表中直接检索或调用系统存储过程间接读取触发器定义信息。

    FOR和AFTER说明CREATE TRIGGER语句所创建的触发器为后触发器。

    INSERT、UPDATE和DELETE定义触发器的触发操作事件，一个触发器由表中的多个事件所触发时，使用INSERT、  DELETE和UPDATE的组合表示，它们相互之间用逗号分隔。

    NOT FORREPLICATION选项说明当复制进程修改触发表中的数据时，不激活所建立的触发器。

    AS于句中的SQL_statemelnts参数为单个SQL语句或语句块，它定义当触发事件发生时，触发器所执行的操作。在触发器定义中不能包含以下TransactSOL语句：

       CREATE  DATABASE、ALTER  DATABASE、DROP  DATABASE等命令。

    在CREATE  TRRGGER语句中，如果触发事件为INSERT或UPDATE操作，可以使用IF子句进一步限制触发器的触发条件，指出只有当指定列的列值被修改时，才激活触发器，从而创建条件插入触发器或条件修改触发器。

    在IF子句中可以使用以下两种格式指出激活条件触发器的被修改列

      UPDATE （列名）［AND ｜OR  UPDATE （列名）］  ［…n］

和

      （COLUMNS_UPDATE ()[位运算符]updated_bitmask）

       第一种方式直接指定激活触发器的被修改列名，而第二种方式则用列的位掩码和比较表达式的方式说明激活触发器的被修改列。其中COLUMNS为触发表中的列名，它说明这一列中的数据被INSERT或UPDATE操作改变时激活触发器。 column所指定列可以是SQL Server支持的所有数据类型，但不能为计算列。

例8.1：创建UPDATE触发器

Create trigger lsq1_trig

on s

for update

as

print 'the table s was updated'

 

执行下述修改语句：

update s

set sname='原料公司分公司'

where sno='s1'

系统自动激发触发器，因此将返回信息“the table s was updated”。

例8.2：创建INSERT触发器

Create trigger lsq2_trig

on s

for insert

as

print 'the table s was inserted'

执行下述插入语句：

insert into s

values('s13','西安印刷厂','79','咸宁路123号')

例8.3：创建DELETE触发器

create trigger lsq_delete

on s

for delete

as

delete s

print 'the table s was deleted'

当执行delete 会出现“the table s wasdeleted”

8.2 修改触发器定义

使用ALTER TRIGGER可以更改原来由 CREATE TRIGGER 语句创建的触发器定义。有关 ALTER TRIGGER 语句所用参数的更多信息，请参见CREATE TRIGGER。

       如：ALTER TRIGGER lsq1_trig on s    

8.3 删除触发器定义

如果不再需要某个触发器，可以将其删除。通过使用DROP TRIGGER语句，即可从从当前数据库表中删除触发器。 DROPTRIGGER语句的命令格式为：

DROP  TRIGGER 触发器名

8.4 查看触发器

若要查看某一特定表上现有的触发器，请使用命令

l sp_helptrigger  数据表名

例如：sp_helptrigger  s命令用来查看S数据表中已经建立好的触发器。

            使用命令：sp_helptext   触发器名，可以查看已经建好的触发器代码。

                                                                                          

          

图22  触发器属性窗口

               
如命令：sp_helptext lsq1_trig，查看已经建好的触发器lsq1_trig中的代码

       其实无论是建立、修改、删除还是查看触发器、触发器代码，均可以通过SQL Sever的“企业管理器”实现，具体的步骤为：

l 右键单击数据表名，在弹出的快捷菜单中选择“所有任务”，执行“管理触发器”命令，如图22。

l 在名称框中选择已经存在的触发器过程或新建一个新的触发器。

l 若为新建一个触发器，则在“文本”中显示定义触发器的模板，若选择一个已经存在的触发器名，则在“文本”中显示定义触发器的代码，可以进行任意编辑，实现修改的目的。

l 也可以单击“删除”按钮，则删除一个触发器的定义。

8.5 触发器的启用和禁用

通过Alter table 语句，可以启用或禁用触发器，而无需从表中删除触发器的定义。由于每个触发器都是在某一表中定义的，因此可以使用AlterTable 语句，而不是使用Alter Trigger。

下面以S表为例说明启用或禁用触发器的方法：

使用命令：

altertable s

disabletrigger lsq1_trig    

就禁用了数据表s中已经定义好的触发器lsq1_trig

使用命令：

altertable s

enabletrigger lsq1_trig

就启用了数据表s中已经定义好的触发器lsq1_trig。