SQL Server触发器

发表评论 / 作者： /

就本质而言，触发器也是一种存储过程，它在特定语言事件发生时自动执行。

1. 触发器概述

在SQL Server数据库系统中，存储过程和触发器都是SQL语句和流程控制语句的集合。

触发器也是一种存储过程，它是一种在基本表被修改时自动执行的内嵌过程，主要通过事件进行触发而被执行，而存储过程可以通过存储过程名字被直接调用。

当对某一张表进行Update、Insert、Delete操作时，SQL Server就会自动执行触发器所定义的SQL语句，从而确保对数据的处理符合由这些SQL语句所定义的规则。

触发器的主要作用是其能实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性，有助于强制引用完整性，以便在添加、更新或删除表中的行时保留表之间已定义的关系。

使用触发器主要可以实现如下操作：

  1. 强制比检测约束更复杂的数据的完整性。
  2. 使用自定义的错误提示信息。
  3. 实现数据库中多张表的级联修改。
  4. 比较数据库修改前后数据的状态。
  5. 调用更多的存储过程。
  6. 维护规范化数据。
2. 触发器的类型

SQL Server包含两大类触发器：DML触发器和DDL触发器。

2.1 DML触发器

当数据库中发生数据操作语言(DML)事件时，将调用DML触发器。

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

系统将触发器和触发它的语句作为可在触发器内回滚的单个事务对待，如果检测到错误(例如，磁盘不足)，则整个事务即自动回滚。

DML触发器经常用于强制执行业务规则和数据完整性。

可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些表之间所定义的关系。

但SQL Server通过alter table和create table语句来提供声明性引用完整性，引用完整性是指有关表的主键和外键之间的关系的规则。

若要强制实现引用完整性，请在alter table和create table中使用主键约束和外键约束。

如果违反了约束，则将回退instead of 触发器操作，并且不激活after触发器。

DML触发器在以下方面非常有用：

  1. DML触发器可以通过数据库中的相关表实现级联更新。不过，通过级联引用完整性约束可以更有效地进行这些更改。
  2. DML触发器可以防止恶意或错误的insert、update以及delete操作，并强制执行比检查约束定义的限制更为复杂的其他限制。与检查约束不同，DML触发器可以引用其他表中的列。
  3. DML触发器可以评估数据修改前后表的状态，并根据该差异采取措施。
  4. 一个表中的多个同类DML触发器允许采取多个不同的操作来响应同一个修改语句。

DML触发器的类型如下：

  • after触发器：这类触发器是在记录已经改变完之后，才会被激活执行，它主要用于记录变更后的处理或检查，一旦发现错误，也可以用rollback transaction语句来回滚本次的操作。
  • instead of 触发器：与after触发器不同，这类触发器一般是用来取代在记录变更之前发生的原本的操作，它并不去执行原来SQL语句里的操作，而去执行触发器本身所定义的操作。
2.2 DDL触发器

DDL触发器将激发以响应各种数据定义语言(DDL)事件，与DML触发器不同的是，它们不会为响应针对表或视图的update、insert和delete语句而触发，相反，它们会为响应多种数据定义语言语句而触发。

这些语句主要是与create、alter、drop、grant、deny、revoke、statistics等T-SQL语句对应。

执行DDL操作的系统存储过程也可以激发DDL触发器。

触发器的作用域取决于事件。

例如，每当数据库中发生create table事件时，都会触发为响应create table事件创建的DDL触发器。

每当服务器发生create login事件时，都会触发为响应create login事件创建的DDL触发器。

数据库范围内的DDL触发器都作为对象存储在创建它们的数据库中。

如果要执行以下操作，可以使用DDL触发器。

  1. 要防止对数据库框架进行某些修改。
  2. 希望根据数据库中发生的操作以响应数据库架构中的更改。
  3. 要记录数据库架构中的更改或事件。

仅在运行触发DDL触发器的DDL语句后，DDL触发器才会激发。

DDL触发器无法作为instead of 触发器使用。

用户可以设计在运行一个或多个特定T-SQL语句后触发的DDL触发器，也可以设计在执行属于一组预定义的相似事件的任何T-SQL事件后触发的DDL触发器。

例如，如果希望在运行create table、alter table或drop table语句后触发的DDL触发器，则可以在create trigger语句中指定for ddl_table_events。

3. 创建触发器

创建触发器时有如下注意事项：

  1. create trigger语句必须是批处理的第一条语句，只能作用于一个表或视图。
  2. 创建触发器的权限默认分配给表的所有者，不能将该权限转给其他用户。
  3. 虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。
  4. 虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。不应引用系统表，而应使用信息架构视图。
  5. 在含有用delete或update操作定义的外键的表中，不能定义instead of 触发器。
  6. truncate table虽然在功能上与delete类似，但是由于truncate删除记录时不被记入事务日志，所以该语句不能激活delete触发器。

创建触发器需要指定下列几项内容：

  • 触发器的名称。
  • 在其上定义触发器的表。
  • 触发器何时被激发。
  • 激活触发器的数据修改语句。

SQL Server中创建触发器的方式有两种，一种是通过图形化界面创建，一种是通过T-SQL语句创建。

3.1 使用图形化界面创建触发器

首先，打开SSMS，如果创建的是DDL触发器，则展开要创建触发器的数据库。

如果创建的是DML触发器，则展开要创建触发器的数据表。

右击”触发器”，在弹出的快捷菜单中选择”新建触发器”命令。

然后，出现创建触发器的T-SQL语句，编辑相关的命令即可。

3.2 使用T-SQL语句创建触发器

语法格式如下：

SQL
CREATE TRIGGER TRIGGER_NAME
ON {TABLE|VIEW}
[WITH ENCRYPTION]
{FOR|AFTER|INSTEAD OF}
{[INSERT][UPDATE][DELETE]}
[NOT FOR REPLICATION]
AS {SQL_STATEMENT[...N]}

其中，各参数说明如下：

  1. TRIGGER_NAME：触发器的名称。TRIGGER_NAME必须遵循标识符规则，且在数据库中必须是唯一的。
  2. TABLE|VIEW：对于执行DML触发器语句的定义文本进行加密处理。
  3. WITH ENCRYPTION：对CREATE TRIGGER语句的定义文本进行加密处理。
  4. FOR|AFTER：AFTER指定DML触发器仅在触发SQL语句中指定的所有操作都已成功执行时才被触发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。不能对视图定义AFTER触发器。其中AFTER可以用FOR来取代。
  5. INSTEAD OF：指定执行触发器而不是执行触发语句，从而代替触发语句的操作。在表或视图上，每个INSERT、UPDATE或DELETE语句最多可以定义一个INSTEAD OF触发器。如果在对一个可更新的视图定义时，使用了WITH CHECK OPTION选项，则INSTEAD OF 触发器不允许在这个视图上定义。
  6. 【INSERT】【UPDATE】【DELETE】：指定数据修改语句，这些语句可以在DML触发器对此表或视图进行尝试时激活该触发器。必须至少指定一个选项，允许使用上述选项的任意顺序组合。
  7. NOT FOR REPLICATION：指示当复制代理修改涉及触发器的表时，不应执行触发器。
  8. SQL_STATEMENT：定义触发器被触发后将执行的数据库操作，指定触发器执行的条件和动作。触发器条件是除引用触发器执行的操作外的附加条件；触发器动作是指当前用户执行激发触发器的某种操作并满足触发器的附加条件时触发器所执行的动作。

例子1：创建DDL触发器防止数据库中的表被删除。

SQL
USE ichi

GO

CREATE TRIGGER PreventDeletion

ON DATABASE

FOR DROP_TABLE

AS

PRINT '数据库中的数据表不允许删除'

ROLLBACK

单击”执行”按钮，显示命令已成功完成。

在数据库中删除某张表：

SQL
USE ichi

GO

DROP TABLE STUDENT

运行上面的代码后，系统会触发PreventDeletion触发器，该触发器显示提示信息，并回滚用户执行的操作，如下图所示：

例子2：在STUDENT表中创建DML触发器，在发生INSERT、UPDATE、DELETE操作时，都会显示表中所有信息。

SQL
USE ichi

GO

CREATE TRIGGER SHOWTABLE
ON STUDENT
FOR INSERT, UPDATE, DELETE
AS
    SELECT *
    FROM   STUDENT

单击”执行”按钮，显示命令已成功完成。

删除STUDENT表中姓名为ICHI的学生信息：

SQL
USE ichi

GO

DELETE FROM STUDENT
WHERE  NAME = 'ICHI'

运行上面的代码后，系统会触发SHOWTABLE触发器，该触发器显示STUDENT表中所有信息，所下图所示：

4. 插入表和删除表

在使用DML触发器的过程中，SQL Server为每个DML触发器提供了两张特殊的临时表，分别是插入表和删除表，它们与创建触发器的表具有相同的结构。

用户可以使用这两张表来检测某些修改操作所产生的影响。

无论是after触发器还是instead of 触发器，触发器被触发时，系统自动为它们创建这两张临时表。

触发器一旦执行完成，这两张表将被自动删除，所以只能在触发器运行期间使用select语句查询到这两张表，但不允许进行修改。

对具有DML触发器的表进行insert、delete和update操作时，过程分别如下：

  • insert操作：系统在原表插入记录的同时，也自动把记录插入inserted临时表中。
  • delete操作：系统在原表删除记录的同时，自动把删除的记录添加到deleted临时表中。
  • update操作：这一事务由两部分组成，首先将旧的数据行从基本表中转移到deleted表中；然后将新的数据行同时插入基本表和inserted临时表中。

例子1：在表STUDENT上创建一个update触发器，如果要把某条记录的姓名更新，但是该姓名在STUDENT2表中已有记录，所以不能更改该姓名，因此不能执行更新操作，并提示用户”此姓名不能更新”

SQL
USE ichi

GO

CREATE TRIGGER TRIGGER_STUDENTNAME
ON STUDENT
FOR UPDATE
AS
    IF EXISTS(SELECT *
              FROM   STUDENT2
              WHERE  NAME = (SELECT NAME
                             FROM   DELETED))
      BEGIN
          PRINT'此姓名不能更改'

          ROLLBACK
      END

单击”执行”按钮，显示命令已成功完成。

把STUDENT表中姓名为ICHI的信息的姓名改为ITACHI

SQL
USE ichi

GO

UPDATE STUDENT
SET    NAME = 'ITACHI'
WHERE  NAME = 'ICHI'

运行上面的代码后，系统会触发TRIGGERT_STUDENTNAME触发器，不能执行更新操作，如下图所示：

例子2：在STUDENT表中建立一个update触发器，实现STUDENT表和STUDENT2表的级联更新，当STUDENT表中记录的学生姓名发送改变时，在STUDENT2表中对应记录的学生姓名也要发生改变。

SQL
USE ichi

GO

CREATE TRIGGER TRIGGER_NAME
ON STUDENT
FOR UPDATE
AS
    UPDATE STUDENT2
    SET    NAME = (SELECT NAME
                   FROM   INSERTED)
    WHERE  NAME = (SELECT NAME
                   FROM   DELETED)

单击”执行”按钮，显示命令已成功完成。

把STUDENT表中的姓名为ITACHI的信息的姓名改为ICHI

SQL
USE ichi

GO

UPDATE STUDENT
SET    NAME = 'ICHI'
WHERE  NAME = 'ITACHI'

运行上面的代码后，系统会触发TRIGGER_NAME触发器，对STUDENT表中数据更新的同时对STUDENT2表中数据进行更新，如下图：

5. 查看触发器

用户可以利用SQL Server提供的系统存储过程sp_help和sp_helptext分别查看触发器的不同信息。

(1)通过sp_help系统存储过程，可以了解触发器的一般信息，包括名字、拥有者名称，类型、创建时间等。

例子1：通过sp_help查看STUDENT表上的触发器TRIGGER_NAME

SQL
USE ichi

GO

EXEC Sp_help
  TRIGGER_NAME

运行结果如下图：

例子2：通过sp_helptext查看STUDENT表上的触发器TRIGGER_NAME

SQL
USE ichi

GO

EXEC sp_helptext
  TRIGGER_NAME

运行结果如下图：

6. 修改触发器

通过图形化界面或T-SQL语句，都可以进行触发器的修改，并且都是修改相关的命令即可。

SQL Server提供了alter trigger语句来修改触发器。

语法格式如下：

SQL
ALTER TRIGGER TRIGGER_NAME
ON {TABLE|VIEW}
[WITH ENCRYPTION]
{FOR|AFTER|INSTEAD OF}
{[INSERT][UPDATE][DELETE]}
[NOT FOR REPLICATION]
AS {SQL_STATEMENT[...N]}

语句中的参数和创建触发器语句中的参数相同。

例子1：修改STUDENT表中的TRIGGER_NAME触发器，仅在发生DELETE操作时，显示表中所有信息。

SQL
USE ichi

GO

ALTER TRIGGER TRIGGER_NAME
ON STUDENT
FOR DELETE
AS
    SELECT *
    FROM   STUDENT

运行结果如图：

7. 禁用、启用触发器

禁用触发器和删除触发器不同，禁用触发器时，触发器只是不会被执行，但是仍然在数据表上定义了该触发器，重新启用触发器后，触发器会被执行。

禁用触发器

禁用触发器后，在执行insert、update或delete语句时，不会执行触发器中的操作。

可以使用disable tigger语句禁用触发器。

语句格式如下：

SQL
ALTER TABLE TABLE_NAME
  DISABLE TRIGGER{ALL|TRIGGER_NAME[,...N]}

各参数说明如下：

  • ALL：表示禁用所有触发器。
  • TRIGGER_NAME：指定触发器的名称。

例子1：禁用STUDENT表上的TRIGGER_NAME

SQL
USE ichi

GO

ALTER TABLE STUDENT
  DISABLE TRIGGER TRIGGER_NAME

单击”执行”按钮，显示命令已成功完成。

启用触发器

已禁用的触发器可以被重新启用。

触发器会以最初被创建的方式触发。

在默认情况下，创建触发器后会启用触发器。

使用ENABLE TRIGGER语句启用触发器。

语法格式如下：

SQL
ALTER TABLE TABLE_NAME
  ENABLE TRIGGER{ALL|TRIGGER_NAME[,..N]}

参数说明和禁用触发器相同。

参数说明和禁用触发器相同。

例子1：启用STUDENT表上的TRIGGER_NAME触发器

SQL
USE ichi

GO

ALTER TABLE STUDENT
  ENABLE TRIGGER TRIGGER_NAME

单击”执行”按钮，显示命令已成功完成。

8. 删除触发器

用户可以删除不再需要的触发器，此时原来的触发表以及表中的数据不受影响。

如果删除表，则表中所有的触发器将被自动删除。

使用DRIO TRIGGER语句删除触发器，其语法格式如下：

SQL
DROP TRIGGER TRIGGER_NAME

例子1：删除STUDENT表上的TRIGGER_NAME

SQL
USE ichi

GO

DROP TRIGGER TRIGGER_NAME

单击”执行”按钮，显示命令已成功完成。

浏览量 5
相关文章：

SQL Server索引

SQL Server存储过程

SQL Server 数据库的并发控制原理-锁原理以及锁类型

SQL Server定时任务

订阅评论
提醒
0 评论
最旧
最新 最多投票
内联反馈
查看所有评论
滚动至顶部