Sql Server 触发器

一、什么是触发器？

触发器（TRIGGER）是数据库对象之一。该对象与编程语言中的函数非常类似，都需要声明、执行等。触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行。

二、创建触发器(语法)

CREATE  TRIGGER trigger_name
 ON [ table | view ]
[ WITH ENCRYPTION ]
     FOR [AFTER , INSTEAD OF ,Insert ,Update ] 
      [ WITH APPEND ]
      [ NOT FOR REPLICATION ]
AS

BEGAIN
      [  IF Update ( column )
          [ { AND | or } Update ( column ) ]
              [ ...n ]
      IF ( COLUMNS_UpdateD ( ) { bitwise_operator } updated_bitmask)
              { comparison_operator } column_bitmask [ ...n ]
       ]
      sql_statement [ ...n ]    

END

参数
trigger_name
是触发器的名称。触发器名称必须符合标识符规则，并且在数据库中必须唯一。可以选择是否指定触发器所有者名称。
Table | view
是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。
WITH ENCRYPTION
加密 syscomments 表中包含 Create TRIGGER语句文本的条目。使用 WITH ENCRYPTION 可防止将触发器作为 SQL Server复制的一部分发布。

AFTER
指定触发器只有在触发 SQL语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。
如果仅指定 FOR 关键字，则 AFTER是默认设置。
不能在视图上定义 AFTER 触发器。
INSTEAD OF
指定执行触发器而不是执行触发 SQL语句，从而替代触发语句的操作。

在表或视图上，每个 Insert、Update 或 Delete语句最多可以定义一个 INSTEAD OF 触发器。然而，可以在每个具有INSTEAD OF 触发器的视图上定义视图。

INSTEAD OF 触发器不能在 WITH CHECK OPTION的可更新视图上定义。如果向指定了 WITH CHECK OPTION选项的可更新视图添加 INSTEAD OF 触发器，SQL Server将产生一个错误。用户必须用 Alter VIEW 删除该选项后才能定义 INSTEADOF 触发器。

{ [Delete] [Insert] [Update] }

是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。

对于 INSTEAD OF 触发器，不允许在具有 ON Delete级联操作引用关系的表上使用 Delete 选项。同样，也不允许在具有 ONUpdate 级联操作引用关系的表上使用 Update 选项。

WITH APPEND

指定应该添加现有类型的其它触发器。只有当兼容级别是 65或更低时，才需要使用该可选子句。如果兼容级别是 70或更高，则不必使用 WITH APPEND子句添加现有类型的其它触发器（这是兼容级别设置为 70 或更高的 CreateTRIGGER 的默认行为）。有关更多信息，请参见sp_dbcmptlevel。

WITH APPEND 不能与 INSTEAD OF触发器一起使用，或者，如果显式声明 AFTER触发器，也不能使用该子句。只有当出于向后兼容而指定 FOR 时（没有INSTEAD OF 或 AFTER），才能使用 WITH APPEND。以后的版本将不支持WITH APPEND 和 FOR（将被解释为 AFTER）。

NOT FOR REPLICATION
表示当复制进程更改触发器所涉及的表时，不应执行该触发器。

AS
是触发器要执行的操作。

sql_statement
是触发器的条件和操作。触发器条件指定其它准则，以确定Delete、Insert 或 Update 语句是否导致执行触发器操作。
当尝试 Delete、Insert 或 Update操作时，Transact-SQL语句中指定的触发器操作将生效。
触发器可以包含任意数量和种类的 Transact-SQL语句。触发器旨在根据数据修改语句检查或更改数据；它不应将数据返回给用户。触发器中的Transact-SQL 语句常常包含控制流语言。Create TRIGGER语句中使用几个特殊的表：
deleted 和 inserted是逻辑（概念）表。这些表在结构上类似于定义触发器的表（也就是在其中尝试用户操作的表）；这些表用于保存用户操作可能更改的行的旧值或新值。例如，若要检索deleted 表中的所有值，请使用：
Select *
FROM deleted

三、触发器的简单实例

常见的触发器有三种：分别应用于Insert , Update , Delete 事件。 

3.1创建Insert事件的触发器

--1）在student上创建<strong>INSERT触发器</strong>stu_insert，要求在student表中插入记录时(要求每次只能插入一条记录)，这个触发器都将更新class表中的class_nun列。并测试触发器stu_insert。CREATE  TRIGGER  stu_insert --触发器名称ON   student  --作用在student表FOR  insert   -- 针对insert事件起作用AsBEGINupdate class set class_num=class_num + 1where class_id=(select class_id from inserted)END

3.2创建Delete事件的触发器

--2）在student上创建<strong>DELETE触发器</strong>stu_delete，要求在student表中删除记录时，这个触发器都将更新class表中的class_nun列。并测试触发器stu_delete。CREATE TRIGGER stu_deleteON studentFOR delete ASBEGINupdate class set class_num=class_num - 1where class_id = (select class_id from deleted)END

3.3 创建Update事件的触发器

USE student  --student为当前的数据库名称GO CREATE TRIGGER stu_Update ON student FOR UPDATE AS IF UPDATE (studentNo) BEGIN RAISERROR ('Transaction cannot be processed.\ ***** studentNo cannot be modified.', 10, 1) ROLLBACK TRANSACTION END 

3.4创建Insand Of Update事件的触发器

Use studentInfogocreate trigger stu_updateon studentinstead of updateasbeginprint '修改学生表'drop trigger stu_update执行语句update studentset stu_id='0601010'where stu_name='张三'

3.5创建数据库的DLL触发器

-- 不允许修改数据库的触发器（删除表或增加表）USE [student_score]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate trigger stu_data1on databasefor drop_table, alter_tableAS IF IS_MEMBER ('db_owner') = 0BEGIN   PRINT 'You must ask your DBA to drop or alter tables!'    ROLLBACK TRANSACTIONENDGOENABLE TRIGGER [stu_data1] ON DATABASEGO

四、触发器的使用限制

<1> 触发器只能创建在永久表上，不能对临时表创建触发器；
<2> 触发器不能使用CALL语句调用具有返回值或使用了动态SQL的存储过程（存储过程可以使用OUT或INOUT参数返回给触发器返回值）。
<3> 触发器中不能使用开启或结束事务的语句段，比如，开始事务（START TRANSACTION）、提交事务（COMMIT）或是回滚事务（ROLLBACK），但是回滚到一个保存点（SAVEPOINT是允许的，因为回滚到保存点不会结束事务）；
<4> 外键不会激活触发器；
<5> 当使用基于行的复制时，从表上的触发器不会因操作主表中的数据而激活。当使用基于语句的复制时，从表上的触发器会被激活。参考 Section 17.4.1.34,“Replication and Triggers”；
<6> 触发器中不允许返回值，因此触发器中不能有返回语句，如果要立即停止一个触发器，应该使用LEAVE语句；

<7> DELETE 触发器不能捕获 TRUNCATE TABLE 语句。 
<8> 触发器中不允许以下 Transact-SQL 语句： 
ALTER DATABASE CREATE DATABASE DISK INIT 
DISK RESIZE DROP DATABASE LOAD DATABASE 
LOAD LOG RECONFIGURE RESTORE DATABASE 
RESTORE LOG 
小结：关于触发器，目前就学习这么多，以后继续补充。欢迎大家提出宝贵建议！