SQL Server 中的触发器

2025年3月17日 | 阅读 10 分钟

在系统内存中，触发器是一组唯一命名的 SQL 语句。它是一种特殊的存储过程，当数据库服务器上发生某些事情时会被自动调用。每个触发器都与一个表相关联。

触发器被称为唯一过程，因为它不能像存储过程那样即时调用。过程和触发器之间的主要区别在于，前者在表成为数据更新事件的主题时被自动触发。相反，存储过程必须直接调用。

使触发器区别于存储过程的主要特性如下

触发器不能手动激活或执行。
触发器没有参数传递的可能性。
触发器会阻止事务的提交或回滚。

触发器语法

我们可以使用 CREATE TRIGGER 语句在 SQL Server 中创建触发器，如下所示

CREATE TRIGGER schema.trigger_name
ON table_name
AFTER  {INSERT, UPDATE, DELETE}
[NOT FOR REPLICATION]
AS
{SQL_Statements}

此语法的参数说明如下

schema: 这是一个可选参数，用于定义新触发器所属的模式。

trigger_name: 这是一个必需参数，用于定义新触发器的名称。

table_name: 这是一个必需参数，用于定义触发器适用的表名。在表名旁边，我们需要写 AFTER 子句，其中可以列出 INSERT、UPDATE 或 DELETE 等任何事件。

NOT FOR REPLICATION: 此选项表示 SQL Server 在作为复制过程一部分修改数据时不会执行触发器。

SQL_Statements: 它包含一个或多个 SQL 语句，用于在发生事件时执行操作。

何时使用触发器？

当我们需要根据期望的条件自动执行特定操作时，触发器非常有用。例如，我们必须了解正在不断变化之中的数据库的更新频率和时间。在这种情况下，我们可能会设计一个触发器，在主表发生更改时将必要的数据插入另一个数据库。

SQL Server 中的触发器示例

让我们通过首先使用以下语句创建一个名为 'Employee' 的表来了解如何在 SQL Server 中使用触发器。

CREATE TABLE Employee
(
  Id INT PRIMARY KEY,
  Name VARCHAR(45),
  Salary INT,
  Gender VARCHAR(12),
  DepartmentId INT
)

接下来，我们将像这样在此表中插入一些记录

INSERT INTO Employee VALUES (1,'Steffan', 82000, 'Male', 3),
(2,'Amelie', 52000, 'Female', 2),
(3,'Antonio', 25000, 'male', 1),
(4,'Marco', 47000, 'Male', 2),
(5,'Eliana', 46000, 'Female', 3)

我们可以使用 SELECT 语句验证插入操作。我们将获得以下输出

我们将创建另一个名为 'Employee_Audit_Test' 的表，以自动存储 Employee 表上的每个操作（如 INSERT、UPDATE 或 DELETE）的事务记录。

CREATE TABLE Employee_Audit_Test
(  
Id int IDENTITY, 
Audit_Action text 
)

现在，我们将创建一个触发器，该触发器将 Employee 表上的每个插入操作的事务记录存储在 Employee_Audit_Test 表中。我们将在下面使用以下语句创建插入触发器。

CREATE TRIGGER trInsertEmployee 
ON Employee
FOR INSERT
AS
BEGIN
  Declare @Id int
  SELECT @Id = Id from inserted
  INSERT INTO Employee_Audit_Test
  VALUES ('New employee with Id = ' + CAST(@Id AS VARCHAR(10)) + ' is added at ' + CAST(Getdate() AS VARCHAR(22)))
END

创建触发器后，我们将尝试将以下记录添加到表中

如果没有发现错误，请执行 SELECT 语句以检查审计记录。我们将获得以下输出

我们将创建另一个触发器，将 Employee 表上的每个删除操作的事务记录存储在 Employee_Audit_Test 表中。我们可以在下面使用以下语句创建删除触发器。

CREATE TRIGGER trDeleteEmployee 
ON Employee
FOR DELETE
AS
BEGIN
  Declare @Id int
  SELECT @Id = Id from deleted
  INSERT INTO Employee_Audit_Test
  VALUES ('An existing employee with Id = ' + CAST(@Id AS VARCHAR(10)) + ' is deleted at ' + CAST(Getdate() AS VARCHAR(22)))
END

创建触发器后，我们将从 Employee 表中删除一条记录

如果没有发现错误，它将显示以下消息

最后，执行 SELECT 语句以检查审计记录

在这两个触发器代码中，您会注意到这些行

SELECT @Id = Id from inserted
SELECT @Id = Id from deleted

这里 inserted 和 deleted 是 SQL Server 使用的特殊表。当您在实际表中插入新行时，inserted 表会保留该行的副本。而 deleted 表会保留您刚从实际表中删除的行的副本。

SQL Server 触发器的类型

我们可以将 SQL Server 中的触发器主要分为三种类型

数据定义语言 (DDL) 触发器
数据操作语言 (DML) 触发器
登录触发器

DDL 触发器

CREATE、ALTER 和 DROP 语句等 DDL 事件会导致 DDL 触发器触发。根据 DDL 事件的类型，我们可以在服务器或数据库级别构建这些触发器。此外，它还可以响应系统建立的执行类似 DDL 任务的某些存储过程。

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

当需要阻止修改数据库模式时
当数据库模式需要审计修改时
当需要修改数据库模式时

DML 触发器

DML 触发器响应用户表或视图中的 INSERT、UPDATE 和 DELETE 语句等 DML 事件而触发。它也可以响应系统定义的存储过程执行的类似 DML 的操作而触发。

DML 触发器可分为两类

后置触发器
Instead Of 触发器

后置触发器

在 SQL Server 成功完成触发操作后，触发该操作的触发器才会触发。通常，当表完成插入、更新或删除操作时，会执行此触发器。它不支持在视图中使用。有时也称为 FOR 触发器。

我们可以将此触发器进一步分为三类

AFTER INSERT 触发器
AFTER UPDATE 触发器
AFTER DELETE 触发器

示例：当我们向表中插入数据时，直到行通过所有约束（如主键约束）后，与该表插入操作关联的触发器才会触发。SQL Server 在数据插入失败时无法触发 AFTER 触发器。

下面是 SQL Server 中 After Triggers 语法的说明

CREATE TRIGGER schema_name.trigger_name
ON table_name
AFTER {INSERT | UPDATE | DELETE}
AS
   BEGIN
      -- Trigger Statements
      -- Insert, Update, Or Delete Statements
   END

Instead of 触发器

Instead of 触发器在 SQL Server 开始执行触发它的触发操作之前触发。这意味着在触发器运行之前不需要进行任何条件约束检查。因此，即使约束检查失败，此触发器也会触发。它与 AFTER 触发器相反。我们可以创建在表上执行成功但未包含表实际插入、更新或删除操作的 INSTEAD OF 触发器。

我们可以将此触发器进一步分为三类

INSTEAD OF INSERT 触发器
INSTEAD OF UPDATE 触发器
INSTEAD OF DELETE 触发器

示例：当我们向表中插入数据时，与该表插入操作关联的触发器将在数据通过所有约束（如主键约束）之前触发。SQL Server 也会在数据插入失败时触发 Instead of Trigger。

下面是 SQL Server 中 Instead of Triggers 语法的说明

CREATE TRIGGER schema_name.trigger_name
ON table_name
INSTEAD OF {INSERT | UPDATE | DELETE}
AS
   BEGIN
      -- trigger statements
      -- Insert, Update, or Delete commands
   END

登录触发器

响应 LOGON 事件的触发器称为登录触发器。每当用户会话在完成登录认证过程但用户会话建立之前，与 SQL Server 实例建立会话时，就会触发 LOGON 事件。因此，触发器生成的任何消息（包括错误和 PRINT 语句消息）都将显示在 SQL Server 错误日志中。登录失败时，登录触发器不会激活。通过记录登录行为或限制某个登录的会话数量，这些触发器可用于审计和管理服务器会话。

如何在 SQL Server 中显示触发器？

当我们有多个数据库和多个表时，显示或列出触发器非常方便。当多个数据库中的表名相同时，此查询非常有帮助。使用以下命令，我们可以查看 SQL Server 中所有可用触发器的列表

SELECT name, is_instead_of_trigger
FROM sys.triggers  
WHERE type = 'TR';

如果我们使用 SQL Server Management Studio，则非常容易显示或列出任何特定表中的所有可用触发器。我们可以通过以下步骤完成此操作

转到数据库菜单，选择所需的数据库，然后展开它。
选择表菜单并展开它。
选择任何特定表并展开它。

在这里，我们将看到各种选项。当我们选择触发器选项时，它会显示此表中所有可用的触发器。

如何在 SQL Server 中更新触发器？

表中的数据会随着时间的推移而改变。在这种情况下，我们也需要对触发器进行更改。我们可以在 SQL Server 中通过两种方式完成此操作。第一种是使用 SQL Server Management Studio，第二种是使用 Transact-SQL 查询。

使用 SSMS 修改触发器

首先，打开 Management Studio 以修改触发器。然后，转到存储触发器的数据库，然后转到表。现在，右键单击您要更改或更新的触发器。这将打开上下文菜单，您将在其中选择修改选项。

选择修改选项后，您将看到一个新的查询窗口，其中自动生成了 ALTER TRIGGER 代码。我们可以根据需要进行更改。

使用 SQL 命令修改触发器

我们可以使用 ALTER TRIGGER 语句修改 MS SQL 中的触发器。以下语句允许我们对触发器进行修改

ALTER TRIGGER [dbo].[triggers_in_sql] 
   ON  [dbo].[EmployeeTable] 
   AFTER INSERT
AS 
BEGIN
 -- Modify as per your needs
END 

如何在 SQL Server 中删除触发器？

我们可以使用 DROP TRIGGER 语句删除 SQL Server 中的现有触发器。删除触发器时必须非常小心。因为一旦删除了触发器，就无法恢复。如果找不到触发器，DROP TRIGGER 语句将引发错误。

以下语法删除 DML 触发器

如果我们想一次删除多个触发器，我们必须使用逗号分隔触发器。

我们可以使用以下格式的 DROP TRIGGER 语句删除一个或多个 LOGON 触发器。

DROP TRIGGER [ IF EXISTS ] trigger_name1, trigger_name2.....n
ON { DATABASE | ALL SERVER };

我们可以使用以下格式的 DROP TRIGGER 语句删除一个或多个 DDL 触发器。

DROP TRIGGER [ IF EXISTS ] trigger_name1, trigger_name2.....n  
ON ALL SERVER;

如果我们使用 SQL Server Management Studio，它使得从表中删除触发器变得非常容易。我们可以通过以下步骤完成此操作

转到数据库 -> 表菜单并展开它。
选择任何特定表，展开它，然后选择触发器选项。

此选项显示此表中所有可用的触发器。现在，右键单击任何您想删除的特定触发器，然后从下拉菜单中选择删除选项。

触发器的优点

以下是使用 SQL Server 中的触发器的优点

触发器设置数据库对象的规则，并在任何更改不满足这些规则时回滚。触发器将检查数据并根据需要进行更改。
触发器有助于我们强制执行数据完整性。
触发器有助于我们在插入或更新数据之前验证数据。
触发器有助于我们记录日志。
触发器提高了 SQL 查询的性能，因为它们不需要每次执行时都进行编译。
触发器减少了客户端代码，节省了时间和精力。
触发器易于维护。

触发器的缺点

以下是使用 SQL Server 中的触发器的缺点

触发器只允许使用扩展验证。
触发器会自动调用，并且用户无法看到它们的执行。因此，很难排查数据库层中发生的情况。
触发器可能会增加数据库服务器的开销。
我们可以在同一个 CREATE TRIGGER 语句中为多个用户操作（如 INSERT 和 UPDATE）定义相同的触发器操作。
我们只能在当前数据库中创建触发器，但它可以引用当前数据库以外的对象。

SQL Server 触发器上的选择题

1. 一种独特的 __________ 过程称为触发器。

视图 (View)
存储的
表
函数

答案：b

解释：触发器与 DDL 和 DML 语句一起用于在修改数据之前或之后分析和评估数据。

2. 请说明以下哪个陈述是正确的。

SQL Server 有三种不同类型的触发器
DDL 触发器是在数据库服务器上发生的数据操作语言 (DML) 事件的响应而计划运行的动作。
数据库对象是触发器
无

答案：c

解释：触发器提供了一类特殊的存储过程，它在与它关联的 DDL 或 DML 语句执行时自动启动。

3. SQL Server 支持多少种不同的触发器？

答案：d

解释：我们可以在 SQL Server 中创建四种不同类型的触发器。数据操作语言 (DML) 触发器、数据定义语言 (DDL) 触发器、CLR 触发器和登录触发器。

4. _ _ _ _ _ _ _ _ 可与 SQL Server 的 AFTER 触发器一起使用。

视图
表
函数
选项 A 和 B 都

答案：d

解释：当 SQL Server 成功完成触发它的操作后，AFTER 触发器会激活。

5. 以下哪个 SQL Server 应用程序可以使用 DML 触发器？

所有选项都正确
删除
插入
更新

答案：a

解释：我们可以创建在 SQL Server 中执行类似 DML 的操作的存储过程，以及对 DML 语句（如 INSERT、UPDATE 和 DELETE）的触发器。

下一个主题SQL Server CAST

← 上一个下一个 →

SQL Server 中的触发器

触发器语法

何时使用触发器？

SQL Server 中的触发器示例

SQL Server 触发器的类型

DDL 触发器

DML 触发器

后置触发器

Instead of 触发器

登录触发器

如何在 SQL Server 中显示触发器？

如何在 SQL Server 中更新触发器？

使用 SSMS 修改触发器

使用 SQL 命令修改触发器

如何在 SQL Server 中删除触发器？

触发器的优点

触发器的缺点

SQL Server 触发器上的选择题

联系信息

关注我们

教程

面试题

在线编译器

Python

Java

.Net Framework

AI, ML and Data Science

Cloud Technology

B.Tech and MCA

Web Technology

PHP

Software Testing

Technical Interview

Java Interview

Python

Web Interview

Database Interview

B.Tech / MCA

Important Interview

Software Testing Interview

Company Interviews

Online Compilers

Multiple Choice Questions

SQL Server 教程

数据库操作

SQL Server 数据库

聚合函数

SQL Server 运算符

SQL Server 子句

SQL Server 键

SQL Server 进阶

杂项

服务

面试题

SQL Server 中的触发器

触发器语法

何时使用触发器？

SQL Server 中的触发器示例

SQL Server 触发器的类型

DDL 触发器

DML 触发器

后置触发器

Instead of 触发器

登录触发器

如何在 SQL Server 中显示触发器？

如何在 SQL Server 中更新触发器？

使用 SSMS 修改触发器

使用 SQL 命令修改触发器

如何在 SQL Server 中删除触发器？

触发器的优点

触发器的缺点

SQL Server 触发器上的选择题

相关帖子

SQL Server WHILE LOOP

SQL Server 中的表变量

SQL Server 添加列

SQL Server 中的索引

SQL Server 临时表

SQL Server OFFSET FETCH

SQL Server 事务

SQL Server 数学函数

SQL Server IDENTITY

SQL Server 日期函数

订阅 Tpoint Tech

联系信息

关注我们

教程

面试题

在线编译器