在本教程中,将学习如何使用SQL Server CREATE TRIGGER
语句来创建新的触发器。
SQL Server CREATE TRIGGER语句简介
CREATE TRIGGER
语句用于创建一个新的触发器,只要针对表发生INSERT,DELETE
或UPDATE等事件,就会自动触发该触发器。
以下是CREATE TRIGGER
语句的语法:
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,用于在事件发生后执行操作。
触发器的“虚拟”表:INSERTED和DELETED
SQL Server提供了两个专门用于名为INSERTED
和DELETED
表的触发器的虚拟表。 SQL Server使用这些表来捕获事件发生之前和之后修改行的数据。
下表显示了INSERTED
和DELETED
表每个事件之前和之后的内容:
DML事件 | INSERTED表持有 | DELETED表持有 |
---|---|---|
INSERT | 要插入的行 | 空 |
UPDATE | 更新修改的新行 | 更新修改的现有行 |
DELETE | 空 | 要删除的行 |
SQL Server CREATE TRIGGER示例
下面来看一个创建新触发器的示例,将使用示例数据库中的production.products
表进行演示。
1. 创建用于记录更改的表
以下语句创建一个名为production.product_audits
的表,以便在针对production.products
表发生INSERT
或DELETE
事件时记录信息:
CREATE TABLE production.product_audits(
change_id INT IDENTITY PRIMARY KEY,
product_id INT NOT NULL,
product_name VARCHAR(255) NOT NULL,
brand_id INT NOT NULL,
category_id INT NOT NULL,
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. 创建之后DML触发器
首先,要创建新触发器,请在CREATE TRIGGER
子句中指定触发器所属的触发器和模式的名称:
CREATE TRIGGER production.trg_product_audit
接下来,在ON
子句中指定触发器将在事件发生时触发的表的名称:
ON production.products
然后,列出将在AFTER子
句中调用触发器的一个或多个事件:
AFTER INSERT, DELETE
触发器的主体以AS
关键字开头:
AS
BEGIN
之后,在触发器的主体内部,将SET NOCOUNT
设置为ON
以禁止在触发触发器时返回受影响的消息行数。
SET NOCOUNT ON;
每当在production.products
表中插入或删除行时,触发器都会在production.product_audits
表中插入一行。 insert
的数据通过UNION ALL运算符从INSERTED
和DELETED
表中提供:
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 AS i
UNION ALL
SELECT
d.product_id,
product_name,
brand_id,
category_id,
model_year,
d.list_price,
getdate(),
'DEL'
FROM
deleted AS d;
以下将所有部分放在一起,构成一个完整的创建语句:
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 (
'产品测试(触发器)',
1,
1,
2019,
999
);
由于INSERT
事件,production.products
表的production.trg_product_audit
触发器被触发。
下面来查看production.product_audits
表的内容:
SELECT
*
FROM
production.product_audits;
执行上面查询语句,得到以下结果:
注:可以多插入几行,再查询
production.product_audits
表中的数据。
接下来,执行以下语句从production.products
表中删除一行,以测试删除记录时触发器的执行:
DELETE FROM
production.products
WHERE
product_id = 328;
正如预期的那样,触发器被触发并将已删除的行插入到production.product_audits
表中:
SELECT
*
FROM
production.product_audits;
执行上面查询语句,得到以下结果:
在本教程中,学习了如何在SQL Server中创建触发器以响应一个或多个事件,例如插入和删除。