触发器

当发生某一事件时，如果满足给定条件，则执行相应的动作

创建触发器

1
2
3
4
5
6
CREATE TRIGGER [schema_name.]trigger_name
ON table_name
AFTER  {[INSERT],[UPDATE],[DELETE]}
[NOT FOR REPLICATION]
AS
{sql_statements}

说明

  • schema_name是新触发器所属模式的名称。模式名称是可选的。
  • trigger_name是要创建触发器的用户定义名称。
  • table_name是触发器作用的表。
  • 事件列在AFTER子句中。事件可以是INSERT，UPDATE或DELETE。单个触发器可以响应针对该表的一个或多个动作而触发。
  • NOT FOR REPLICATION选项指示SQL Server在复制过程中进行数据修改时不触发触发器。
  • sql_statements是一个或多个Transact-SQL，用于在事件发生后执行操作。

触发器的虚拟表

SQL Server提供了两个专门用于名为INSERTED和DELETED表的触发器的虚拟表。

SQL Server使用这些表来捕获事件发生之前和之后修改行的数据。

下表显示了INSERTED和DELETED表每个事件之前和之后的内容：

DML事件 INSERTED表持有 DELETED表持有
INSERT 要插入的行 空
UPDATE 更新修改的新行 更新修改的现有行
DELETE 空 要删除的行

案例

创建一张新表，用于存放Student表的插入与删除操作的日志信息

1
2
3
4
5
6
7
8
9
10
CREATE TABLE Student_logs(
    change_id INT IDENTITY PRIMARY KEY,
    change_date DATETIME NOT NULL,
    Sno char(9) NOT NULL,
    Sname char(20) NOT NULL,
    Ssex char(2),
    Sage smallint,
    Sdept char(20),
    operation char(3) NOT NULL CHECK(operation='INS' OR operation='DEL')
);

创建触发器

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TRIGGER trg_student_logs
ON Student
AFTER INSERT,DELETE
AS
BEGIN
INSERT INTO Student_logs(
Sno,Sname,Ssex,Sage,Sdept,operation
)
SELECT GETDATE(),Sno,Sname,Ssex,Sage,Sdept,'INS' FROM INSERTED
UNION ALL
SELECT GETDATE(),Sno,Sname,Ssex,Sage,Sdept,'DEL' FROM DELETED
END

image-20220823140407373

INSTEAD OF触发器

INSTEAD OF触发器是一种触发器，用于跳过对表或视图的INSERT，DELETE或UPDATE语句，并执行触发器中定义的其他语句。根本不会发生实际的插入，删除或更新操作。

换句话说，INSTEAD OF触发器会跳过DML语句并执行其他语句。

1
2
3
4
5
CREATE TRIGGER [schema_name.] trigger_name
ON {table_name | view_name }
INSTEAD OF {[INSERT] [,] [UPDATE] [,] [DELETE] }
AS
{sql_statements}

说明

  • 首先，在CREATE TRIGGER子句中指定触发器的名称以及触发器所属的模式的名称。
  • 其次，指定触发器关联的表或视图的名称。
  • 第三，指定触发器将在INSTEAD OF子句中触发的事件，如INSERT，DELETE或UPDATE。 可以调用触发器以响应一个或多个事件。
  • 第四，将触发器主体放在AS关键字之后。 触发器的主体可能包含一个或多个Transact-SQL语句。

实例

使用INSTEAD OF触发器的典型示例是覆盖视图上的插入，更新或删除操作。

假设，应用程序需要将新品牌插入到production.brands表中。 但是，在插入到production.brands表之前，新品牌应存储在另一个名为production.brand_approvals的表中以供批准。

为此，需要为应用程序创建名为production.vw_brands的视图以插入新品牌。 如果将品牌插入到视图中，则会触发INSTEAD OF触发器以将品牌插入到production.brand_approvals表中。

具体演示请见原文

DDL触发器

SQL Server DDL触发器响应服务器或数据库事件而不是表数据修改。 这些事件由Transact-SQL语句创建，通常以以下关键字之一：CREATE，ALTER，DROP，GRANT，DENY，REVOKE或UPDATE STATISTICS开头。

1
2
3
4
5
CREATE TRIGGER trigger_name
ON { DATABASE |  ALL SERVER}
[WITH ddl_trigger_option]
FOR {event_type | event_group }
AS {sql_statement}

说明

  • trigger_name - 在CREATE TRIGGER关键字后指定用户定义的触发器名称。 请注意，不必为DDL触发器指定模式，因为它与实际的数据库表或视图无关。
  • DATABASE | ALL SERVER - 如果触发器响应数据库范围的事件，则使用DATABASE;如果触发器响应服务器范围的事件，则使用ALL SERVER。
  • ddl_trigger_option - 用于指定ENCRYPTION和/或EXECUTE AS子句。 ENCRYPTION加密触发器的定义。 EXECUTE AS定义执行触发器的安全上下文。
  • event_type | event_group - 表示导致触发器触发的DDL事件，例如，CREATE_TABLE，ALTER_TABLE等。
  • event_group是一组event_type事件，例如DDL_TABLE_EVENTS。
  • 触发器可以订阅一个或多个事件或事件组。

作用

DDL触发器在以下情况下很有用：

  • 记录数据库模式中的更改。
  • 防止对数据库模式进行某些特定更改。
  • 响应数据库模式的更改。

实例

见原文

禁用触发器

有时，为了进行故障排除或数据恢复，可能需要暂时禁用触发器。

1
2
DISABLE TRIGGER [schema_name.][trigger_name] 
ON [object_name | DATABASE | ALL SERVER]

说明

  • 首先，指定触发器所属的模式名称以及DISABLE TRIGGER关键字后要禁用的触发器的名称。
  • 其次，如果触发器是DML触发器，请指定触发器绑定的表名或视图。 如果触发器是DDL数据库范围的触发器，则使用DATABASE;如果触发器是DDL服务器范围的触发器，则使用ALL SERVER。

实例

创建一个简单的显示信息的触发器，然后禁止它。

1
2
3
4
5
CREATE TRIGGER prt_stu
ON Student
INSTEAD OF INSERT
AS
PRINT "禁止插入！"

image-20220823145015026image-20220823145026560

禁止该触发器

1
2
DISABLE TRIGGER prt_stu
ON Student

image-20220823145638707

成功禁用了！

启用触发器

1
2
ENABLE TRIGGER [schema_name.][trigger_name] 
ON [object_name | DATABASE | ALL SERVER]

实例

1
2
ENABLE TRIGGER prt_stu
ON Student

image-20220823150520259

image-20220823150530615

成功启用！

查看触发器定义

通过从系统视图查询获取触发器定义

1
2
3
4
5
6
SELECT 
    definition   
FROM 
    sys.sql_modules  
WHERE 
    object_id = OBJECT_ID('[trigger_name]');

image-20220823151126772

使用OBJECT_DEFINITION函数获取触发器定义

1
2
3
4
5
6
SELECT 
    OBJECT_DEFINITION (
        OBJECT_ID(
            '[trigger_name]'
        )
    ) AS trigger_definition;

image-20220823152149157

结果不全，因为我设置了“查询选项”每列最多256字符。

使用sp_helptext存储过程获取触发器定义

1
EXEC sp_helptext '[trigger_name]' ;

image-20220823152457404

使用SSMS获取触发器定义

在图形化界面中找到对应的触发器，右键点击修改。

image-20220823152635789

列出所有触发器

1
2
3
4
5
6
7
SELECT  
    name,
    is_instead_of_trigger
FROM 
    sys.triggers  
WHERE 
    type = 'TR';

image-20220823153032804

删除触发器

DROP TRIGGER语句用于从数据库中删除一个或多个触发器

1
DROP TRIGGER [ IF EXISTS ] [schema_name.]trigger_name [ ,...n ];

说明

  • IF EXISTS仅在已经存在的情况下有条件地移除触发器。
  • schema_name是DML触发器所属的模式名称。
  • trigger_name是将要删除的触发器的名称。

如果要一次删除多个触发器，则需要用逗号分隔触发器。

要删除一个或多个DDL触发器

1
2
DROP TRIGGER [ IF EXISTS ] trigger_name [ ,...n ]   
ON { DATABASE | ALL SERVER };

说明

  • DATABASE表示DDL触发器的范围适用于当前数据库。
  • ALL SERVER指示DDL触发器的范围适用于当前服务器。

要删除LOGON事件触发器

1
2
DROP TRIGGER [ IF EXISTS ] trigger_name [ ,...n ]   
ON ALL SERVER;

请注意，当删除表时，与表关联的所有触发器也会自动删除。