SQL Server 触发器(Triggers)
目录- SQL Server 触发器(Triggers)
简介
SQLServer触发器是响应数据库对象、数据库和服务器事件自动执行的特殊存储过程。SQL Server提供了三种类型的触发器：
- 数据操作语言（DML）触发器，用于对表的
INSERT、UPDATE和DELETE事件时而自动调用。 - 数据定义语言（DDL）触发器，用于响应
CREATE、ALTER和DROP语句。DDL触发器也会响应某些执行类似DDL操作的系统存储过程而触发。 - 响应Logon事件触发的登录触发器
创建AFTER触发器（DML）
简介
AFTER 触发器（事后触发器）
AFTER 触发器定义了对表执行了 INSERT、UPDATE 或 DELETE 语句操作之后再执行的操作。比如对某个表中的数据进行了更新操作后，要求立即对相关的表进行指定的操作，这时就可以采用 AFTER 触发器。AFTER 触发器只能在表上指定，且动作晚于约束处理。
每一个表上只能创建一个 INSTEAD OF 触发器，但可以创建多个 AFTER 触发器。
SQL Server CREATE TRIGGER语句
CREATE TRIGGER语句创建一个新触发器，当表发生INSERT、DELETE或UPDATE等事件时，该触发器将自动触发。
语法：
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是一个或多个T-sql，用于在事件发生后执行操作。
触发器的“虚拟”表：INSERTED和DELETED
SQLServer为触发器提供了两个虚拟表，名为INSERTED和DELETED表。SQLServer使用这两个表来捕获事件发生前后修改行的数据。
下表显示了每个事件前后INSERTED和DELETED表的内容：
| 操作 | INSERTED表内容 | INSERTED表内容 |
|---|---|---|
| INSERT | 要插入的行 | 空 |
| UPDATE | 更新修改的新行 | 被更新修改的现有行： |
| DELETE | 空 | 要删除的行 |
创建触发器示例
使用如下产品表用于示例：
1）创建用于记录更改的审计表
先创建了一个名为production.product_audits的表。用于在production.products表发生INSERT或DELETE事件时记录信息：
CREATE TABLE production.product_audits(
change_id INT IDENTITY PRIMARY KEY, --主键
product_id INT NOT NULL, --产品Id
product_name VARCHAR(255) NOT NULL, --产品名
brand_id INT NOT NULL, --品牌Id
category_id INT NOT NULL, --分类Id
model_year SMALLINT NOT NULL, --那一年的产品
list_price DEC(10,2) NOT NULL, --标价
updated_at DATETIME NOT NULL, --更新时间
operation CHAR(3) NOT NULL, --操作方式
CHECK(operation = 'INS' or operation='DEL')
);
production.product_audits表包含production.products(产品)表的所有列，此外，还有一些列来记录更改，例如updated_at、operation和change_id。
注意，对于重要的数据表，最好是做这样一个审计表来记录对数据的修改，最好再添加上修改人。
2）创建一个AFTERDML触发器
首先，要创建新触发器，请在CREATE TRIGGER子句中指定触发器的名称和触发器所属的模式：
CREATE TRIGGER production.trg_product_audit
然后再指定触发器对应的表：
ON production.products
然后，在AFTER子句中列出将触发触发器的一个或多个事件：
AFTER INSERT, DELETE
触发器的主体逻辑代码以AS关键字开头：
AS
BEGIN
然后，在触发器的主体中，将SET NOCOUNT设置为ON，以禁止在触发触发器时返回受影响的行数。
SET NOCOUNT ON;
每当一行插入production.products表或从production.products表中删除时，触发器将在production.product_audits表中插入一行。插入的数据通过UNION ALL运算符从INSERTED和DELETED表中提供：
完整代码：
CREATE TRIGGER production.trg_product_audit
ON production.products
AFTER INSERT, DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO production.product_audits(
product_id,
product_name,
brand_id,
category_id,
model_year,
list_price,
updated_at,
operation
)
SELECT
i.product_id,
product_name,
brand_id,
category_id,
model_year,
i.list_price,
GETDATE(),
'INS'
FROM
inserted i
UNION ALL
SELECT
d.product_id,
product_name,
brand_id,
category_id,
model_year,
d.list_price,
GETDATE(),
'DEL'
FROM
deleted d;
END
最后，执行整个语句来创建触发器。创建触发器后，您可以在表的触发器文件夹下找到它，如下图所示：
3）测试触发器
咱们对表production.products插入一个新行：
INSERT INTO production.products(
product_name,
brand_id,
category_id,
model_year,
list_price
)
VALUES (
'Test product',
1,
1,
2018,
599
);
由于是INSERT事件，前面创建的触发器被触发。
我们来检查一下production.product_audits表
SELECT
*
FROM
production.product_audits;
没有问题，确实有一条插入记录
然后我们来从production.products表删除一行：
DELETE FROM
production.products
WHERE
product_id = 322;
触发器应该也被触发
检查一下审计表production.product_audits：
SELECT
*
FROM
production.product_audits;
果然有一条删除记录，没有任何问题。
创建INSTEAD OF触发器（DML）
简介
INSTEAD OF 触发器（替代触发器）
INSTEAD OF 触发器用来代替通常的触发动作，即当对表进行INSERT、UPDATE 或 DELETE 操作时，系统不是直接对表执行这些操作，而是把操作内容交给触发器，让触发器检查所进行的操作是否正确。如正确才进行相应的操作。因此，INSTEAD OF 触发器的动作要早于表的约束处理。
INSTEAD OF 触发器的操作有点类似于完整性约束。在对数据库的操纵时，有些情况下使用约束可以达到更好的效果，而如果采用触发器，则能定义比完整性约束更加复杂的约束。
INSTEAD OF 触发器不仅可在表上定义，还可在带有一个或多个基表的视图上定义，但在作为级联引用完整性约束目标的表上限制应用。
创建语法
CREATE TRIGGER [schema_name.] trigger_name
ON {table_name | view_name }
INSTEAD OF {[INSERT] [,] [UPDATE] [,] [DELETE] }
AS
{sql_statements}
其中：
- 首先
schema_name是新触发器所属的架构的名称。架构名称是可选的。trigger_name是新触发器的名称。 - 其次，指定触发器关联的表(
table_name)或视图(view_name)的名称。 - 第三，指定触发器将在
INSTEAD OF子句中触发的事件，如INSERT、DELETE或UPDATE。可以响应一个或多个事件。 - 第四，将触发器主体放在
AS关键字之后。触发器的主体可以由一个或多个T-SQL语句组成。
示例
使用INSTEAD OF触发器的典型示例是覆盖视图上的插入、更新或删除操作。
假设一个应用程序需要在品牌表（production.brands）中插入一个新品牌。
然而，新品牌应该存储在另一个称为production.brand_approvals（品牌审核）的表中。production.brand_approvals表用于在插入production.brands表之前获得批准审核。
production.brand_approvals表中的品牌审核通过了再插入到production.brands表
要实现这一点，您需要创建一个名为production.vw_brands的视图。production.vw_brands用于应用程序插入新品牌。如果将品牌插入视图，将触发INSTEAD OF触发器来将品牌插入production.brand_approvals审核表。
过程如下：
先创建production.brand_approvals审核表用于存储待审批的品牌:
CREATE TABLE production.brand_approvals(
brand_id INT IDENTITY PRIMARY KEY,
brand_name VARCHAR(255) NOT NULL
);
再创建production.vw_brands品牌视图。对应production.brands和production.brand_approvals表：
CREATE VIEW production.vw_brands
AS
SELECT
brand_name,
'Approved' approval_status
FROM
production.brands
UNION
SELECT
brand_name,
'Pending Approval' approval_status
FROM
production.brand_approvals;
一旦一行插入到production.vw_brands视图，我们通过以下INSTEAD OF触发器将其路由到production.brand_approvals表：
CREATE TRIGGER production.trg_vw_brands
ON production.vw_brands
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO production.brand_approvals (
brand_name
)
SELECT
i.brand_name
FROM
inserted i
WHERE
i.brand_name NOT IN (
SELECT
brand_name
FROM
production.brands
);
END
如果production.brands表中不存在新插入的品牌名称，触发器就将新品牌名称插入production.brand_approvals表等待审核。
测试一下，我们向production.vw_brands视图中插入一个新品牌：
INSERT INTO production.vw_brands(brand_name)
VALUES('Eddy Merckx');
此INSERT语句触发了INSTEAD OF触发器以将新行插入到production.brand_approvals表。
此时如果从production.vw_brands视图中查询数据将看到一个新行：
SELECT
brand_name,
approval_status
FROM
production.vw_brands;
再来看看品牌审核表：
SELECT
*
FROM
production.brand_approvals;
SQL Server DDL触发器
简介
SQLServer DDL触发器响应服务器或数据库事件，而不是表数据修改。这些事件由T-SQL语句创建，通常以以下关键字之一CREATE、ALTER、DROP、GRANT、DENY、REVOKE或UPDATE STATISTICS开头。
比如，只要用户执行CREATE TABLE或ALTER TABLE语句，就可以编写DDL触发器来记录日志。
DDL触发器在以下情况下很有用：
- 记录数据库架构（schema）中的更改。
- 阻止对数据库架构进行某些特定更改。
- 响应数据库架构中的更改。
语法：
CREATE TRIGGER trigger_name
ON { DATABASE | ALL SERVER}
[WITH ddl_trigger_option]
FOR {event_type | event_group }
AS {sql_statement}
trigger_name
触发器名字，注意不用指定架构名，因为DDL触发器与实际的表或者视图无关
DATABASE | ALL SERVER
触发器作用范围，如果触发器响应数据库范围的事件，则使用DATABASE；如果触发器响应服务器范围的事件则使用ALL SERVER。
ddl_trigger_option
ddl_trigger_option指定ENCRYPTION和/或EXECUTE AS子句。ENCRYPTION加密触发器的定义。EXECUTE AS定义执行触发器的安全上下文。
event_type | event_group
event_type表示导致触发器触发的DDL事件，例如CREATE_TABLE、ALTER_TABLE等。
event_group是一组event_type事件，如DDL_TABLE_EVENTS。
触发器可以订阅一个或多个事件或事件组。
示例
假设我们想捕获对数据库索引所做的所有修改，方便更好地监视与这些索引更改相关的数据库服务器的性能。
首先，创建一个名为index_logs的新表来记录索引更改：
CREATE TABLE index_logs (
log_id INT IDENTITY PRIMARY KEY,
event_data XML NOT NULL,
changed_by SYSNAME NOT NULL
);
GO
然后，创建一个DDL触发器来跟踪索引更改并将事件数据插入index_logs表：
CREATE TRIGGER trg_index_changes
ON DATABASE
FOR
CREATE_INDEX,
ALTER_INDEX,
DROP_INDEX
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO index_logs (
event_data,
changed_by
)
VALUES (
EVENTDATA(),
USER
);
END;
GO
在触发器的主体代码中，使用了EVENTDATA()函数，该函数返回有关服务器或数据库事件的信息。该函数仅在DDL或登录触发器内可用。
然后，为sales.customers表的first_name和last_name列创建索引：
CREATE NONCLUSTERED INDEX nidx_fname
ON sales.customers(first_name);
GO
CREATE NONCLUSTERED INDEX nidx_lname
ON sales.customers(last_name);
GO
然后，查询index_logs表中的数据，检查触发器是否正确捕获了索引创建事件:
SELECT
*
FROM
index_logs;
如果单击event_data列的单元格，可以查看事件的详细XML数据：
禁用触发器
简介
有时，出于故障排除或数据恢复的目的，可能需要暂时禁用触发器。为此，可以使用DISABLE TRIGGER语句：
DISABLE TRIGGER [schema_name.][trigger_name]
ON [object_name | DATABASE | ALL SERVER]
其中：
- 首先，在
DISABLE TRIGGER之后指定触发器所属架构的名称和要禁用的触发器的名称。 - 其次，如果触发器是DML触发器，则指定触发器绑定到的表名或视图。如果触发器是DDL数据库范围的触发器，则使用
DATABASE；如果触发器为DDL服务器范围的触发器则使用ALL SERVER。
禁用单个触发器
创建一个会员表用于演示：
CREATE TABLE sales.members (
member_id INT IDENTITY PRIMARY KEY,
customer_id INT NOT NULL,
member_level CHAR(10) NOT NULL
);
下面创建一个触发器，每当新行插入到sales.members中时就会触发该触发器。出于演示目的，触发器只返回一条简单的消息。
CREATE TRIGGER sales.trg_members_insert
ON sales.members
AFTER INSERT
AS
BEGIN
PRINT 'A new member has been inserted';
END;
现在尝试在表中插入一行：
INSERT INTO sales.members(customer_id, member_level)
VALUES(1,'Silver');
由于INSERT事件，触发器被触发并打印出以下消息：
A new member has been inserted
现在使用如下语句关闭该触发器：
DISABLE TRIGGER sales.trg_members_insert
ON sales.members;
现在再在会员表插入新行，将不会触发触发器：
INSERT INTO sales.members(customer_id, member_level)
VALUES(2,'Gold');
但是触发器定义仍然在表中。如果在SQL Server Management Studio（SSMS）中查看触发器，将注意到禁用的触发器名称上有一个红色十字图标：
禁用表中的所有触发器
语法：
DISABLE TRIGGER ALL ON table_name;
只需指定表的名称即可禁用属于该表的所有触发器。
禁用数据库中的所有触发器
DISABLE TRIGGER ALL ON DATABASE;
启用触发器
简介
语法：
ENABLE TRIGGER [schema_name.][trigger_name]
ON [object_name | DATABASE | ALL SERVER]
其中：
- 首先，指定要启用的触发器的名称。也可以指定触发器所属的架构的名称。
- 其次，如果触发器是DML触发器，需指定触发器所属的表。如果触发器是DDL数据库范围的触发器，则使用
DATABASE；如果触发器是DDL服务器范围的触发器则使用ALL SERVER。
示例
启用表中单个触发器
比如要启用sales.members表中的触发器sales.trg_members_insert:
ENABLE TRIGGER sales.trg_members_insert
ON sales.members;
启用后，可以通过SQL Server Management Studio查看触发器的状态，如下图所示：
启用表中所有触发器
ENABLE TRIGGER ALL ON table_name;
只需要指定表名就可以了
比如要启用sales.members表所有触发器：
ENABLE TRIGGER ALL ON sales.members;
下图为sales.members的所有触发器的状态：
启用数据库的所有触发器
ENABLE TRIGGER ALL ON DATABASE;
查看触发器的定义
通过查询系统视图
可以通过sys.sql_modules视图查看触发器定义：
SELECT
definition
FROM
sys.sql_modules
WHERE
object_id = OBJECT_ID('sales.trg_members_delete');
在这个查询中，将要获取定义的触发器的名称传递给WHERE子句中的OBJECT_ID()函数。
使用OBJECT_definition函数
SELECT
OBJECT_DEFINITION (
OBJECT_ID(
'sales.trg_members_delete'
)
) AS trigger_definition;
在这个查询中，将触发器名称传递给OBJECT_ID函数以获取触发器的ID。然后，使用OBJECT_DEFINITION()函数根据触发器的ID获取触发器定义的T-SQL源代码。
使用sp_helptext存储过程
EXEC sp_helptext 'sales.trg_members_delete' ;
sp_helptext存储过程返回已创建对象（在本例中为触发器）的定义。
通过SSMS
要查看DML触发器的定义：
在如下图中表的触发器目录下，右击对应触发器，然后点击Modify（修改），即可查看触发器定义：
列出所有触发器
要列出SQL Server中的所有触发器，可以从sys.triggers视图中查询数据：
SELECT
name,
is_instead_of_trigger
FROM
sys.triggers
WHERE
type = 'TR';
输出：
删除触发器
简介
SQL Server DROP TRIGGER语句从数据库中删除一个或多个触发器。以下是删除DML触发器的DROP TRIGGER语句的语法:
DROP TRIGGER [ IF EXISTS ] [schema_name.]trigger_name [ ,...n ];
如果要同时删除多个触发器，则需要用逗号分隔。
要删除一个或多个DDL触发器，请使用以下形式的DROP TRIGGER语句：
DROP TRIGGER [ IF EXISTS ] trigger_name [ ,...n ]
ON { DATABASE | ALL SERVER };
DATABASE表示DDL触发器的作用域应用于当前数据库。ALL SERVER表示应用于当前服务器的DDL触发器的范围。
要删除LOGON事件触发器，使用以下语法：
DROP TRIGGER [ IF EXISTS ] trigger_name [ ,...n ]
ON ALL SERVER;
注意，当删除一个表时，与该表关联的所有触发器也会自动删除。
示例
A）删除一个DML触发器
删除名为sales.trg_members_insert触发器：
DROP TRIGGER IF EXISTS sales.trg_member_insert;
B）删除一个DDL触发器
删除trg_index_changes触发器：
DROP TRIGGER IF EXISTS trg_index_changes;