SQL Server 触发器简介

SQL Server 触发器用于在对表执行 INSERT、DELETE 或 UPDATE 操作之后或之前执行。您可以将它们用于视图或表来执行上述活动。请记住，您只能将触发器关联到一个表。

我们可以将触发器称为一种特殊类型的存储过程，当数据库对象发生特定事件时，它会自动运行。这可以是 DDL 或 DML 操作。

SQL Server 触发器类型

触发器有两种类型，即 AFTER 和 INSTEAD OF。

SQL Server AFTER 触发器

after/for 触发器在 INSERT、DELETE 或 UPDATE 之后在表上运行。这意味着在存储过程开始运行之前，所有操作都应执行，并且语句还必须在检查约束中成功。

在 SQL Server 中，After 触发器类型不支持视图，因此请仅在表上使用它们。它可以进一步细分为

  1. AFTER INSERT：在 Employee 表完成插入数据操作后触发。一旦它完成插入 Employee 表，它将开始插入到审计表中。例如，如果它未能插入到 Employee 表，那么它就不会插入到 Audit 表。
  2. AFTER UPDATE：在 Employees 表上的 Update 操作完成后触发。一旦它完成 Employee 表的更新，它将开始插入/更新到审计表中。例如，如果它未能更新 Employees 表，那么它就不会插入到 Audit 表。
  3. AFTER DELETE：在 Employee 表上的 Delete 语句操作完成后，After delete 触发。一旦它完成从 Employees 表中删除记录，它将开始从审计表中插入/删除。例如，如果它未能从 Employee 表中删除，那么它就不会插入到 Audit 表。

SQL Server INSTEAD OF 触发器

Instead of 触发器在 INSERT、DELETE 或 UPDATE 操作在表上执行之前触发。

这意味着在 SQL Server 的 instead of 触发器类型开始运行之前，它不需要任何条件约束检查。因此，即使约束检查失败，此存储过程也会执行。它可以进一步细分为

  • INSTEAD OF INSERT：Instead of insert 触发器将在 Employee 表上的 Insert 操作开始之前触发。一旦它完成插入 Employee Audit 表，它将开始插入到 Employee 表。如果由于某种原因失败，它将不会插入到 Employee 表中。
  • INSTEAD OF UPDATE：在更新 Employee 表中的记录之前触发。一旦完成执行，它将开始更新 Employee 表中的记录。如果失败，它将不会更新表。
  • INSTEAD OF DELETE：在开始从 Employee 表删除记录之前触发。一旦成功执行，SQL 将开始从 Employees 表中删除记录。如果失败，它将不会从 Employee 表中删除任何记录。

SQL Server 中的数据定义语言触发器

数据定义语言触发器响应 DDL 事件（包括 CREATE、ALTER、DROP 等）触发。我们可以使用它们来防止架构更改并记录数据库架构中发生的事件。此 DDL 触发器的一个示例是在删除数据库或表时执行某些操作或触发。

SQL Server 中的数据操作语言触发器

DML 触发器响应数据操作语言事件（包括 INSERT、DELETE 和 UPDATE 语句）触发。它们是存储过程的特殊类型，是数据完整性的第一步。如果进一步细分数据操作语言或 DML 触发器，它们是 Instead of 和 after。

登录触发器

顾名思义，登录触发器将在用户尝试登录或建立服务器连接时触发。

SQL Server 中的 CREATE TRIGGER 语句

After Triggers 的语法如下所示。

CREATE [OR ALTER] TRIGGER [Schema_Name].Trig_Name
ON Table
AFTER INSERT | UPDATE | DELETE
AS
   BEGIN
      -- Statements
      -- Insert, Update, Or Delete Statements
   END
  • Schema_name：请指定模式名称，例如 dbo 或 Human Resources 等。
  • Trig_Name：您可以指定任何您希望使用的名称，但不能是系统保留关键字。请尽量使用有意义的名称，以便您能够轻松识别它们。

创建 SQL Server Instead Of Triggers 类型的基本语法如下所示。

CREATE [OR ALTER] TRIGGER [Schema_Name].Trig_Name
ON Table | View
INSTEAD OF INSERT | UPDATE | DELETE
AS
   BEGIN
      -- Statements
      -- Insert, Update, Or Delete Statements
   END

从上面的语句可以看出，它与我们之前使用的创建语句相同，但我们添加了 INSTEAD OF INSERT | UPDATE | DELETE。

让我们通过一个示例了解如何创建触发器、修改现有触发器以及删除它们。为此，我们将使用 Employee 和 audit 表进行演示。如您所见，emp 和 audit 表都是空的。

SQL Server 触发器创建示例

我们可以通过两种方式创建它们。此示例向您展示了使用 Management Studio (SSMS) 创建它的步骤。在开始生成任何内容之前，让我们看看我们的数据库是否已存在任何触发器。

要查看现有触发器，请选择 Database 对象 -> 展开指定的表名（存在触发器的表）-> 转到 Triggers 文件夹并展开它。从下面的屏幕截图可以看出，我们的 Employee 表下没有任何触发器。

右键单击 Triggers 文件夹将打开上下文菜单。请从中选择 New.. 选项，如下图所示。

Create a New Trigger in SQL Server Management Studio

单击该选项后，它将打开一个新的查询窗口，其中包含默认模板，如下图所示。

Auto Generated Trigger code

在此，您可以添加 Schema 名称、Trig_name、TableName、数据修改语句以及您想要应用的语句。

使用事务查询创建

在此演示中，我们将创建一个 SQL Server After Insert Trigger，用于将记录与审计信息一起插入到 Employee Table Audit 中。

CREATE TRIGGER ExampleTR 
   ON  EmployeeTR 
   AFTER INSERT
AS 
BEGIN
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
	SET NOCOUNT ON;

     -- Insert statements here
	INSERT INTO [Audit]( 
       [ID]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[ServerName]
      ,[ServerInstanceName]
      ,[Insert Time])
SELECT  ID,
	Name,
	Education,
	Occupation,
	YearlyIncome,
	Sales,
	CAST( SERVERPROPERTY('MachineName') AS VARCHAR(50)), 
	CAST( SERVERPROPERTY('ServerName') AS VARCHAR(50)), 
	GETDATE()
FROM INSERTED;
PRINT 'We Successfully Fired!.'
END
GO

我们在上一篇文章中已经解释了其中的语句。我建议您参考 AFTER INSERT 文章来理解此代码。

运行上面的查询

Messages
--------
Command(s) completed successfully.

让我通过打开 Object Explorer -> 转到 Database -> 转到并展开 Employee -> 然后展开 Folder 来向您展示新创建的触发器。

Create Triggers in SQL Server using Transact Query 6

为了演示，我将向 Employee 表插入 5 条随机记录。这将帮助我们检查 After insert 是否已触发。

INSERT INTO [EmployeeTR] (
		[Name]
	       ,[Education]
	       ,[Occupation]
	       ,[YearlyIncome]
	       ,[Sales]
	     )
VALUES ('Tutorial Gateway', 'Masters Degree', 'Admin', 250000, 1900)
      ,('Aamir Khan', 'Bachelors', 'Skilled Professional', 69000, 100)
      ,('Lara Diaz', 'Bachelors', 'Management', 85000, 60)
      ,('Mahesh kumar', 'Degree', 'Professional', 45000, 630)
      ,('Ruiz John', 'Post Graduate', 'Clerical', 40000, 220)

从上面的屏幕截图可以看出，我们的 SQL Server 触发器已触发。此外，它还会打印我们在其中放置的消息。请使用以下查询检查 Employee 表中插入的记录。

SELECT [ID]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeTable]

接下来，使用以下查询检查 Audit 表中的记录。

SELECT [ID]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[ServerName]
      ,[ServerInstanceName]
      ,[Insert Time]
  FROM [EmployeeTableAudit]

SQL Server Alter Trigger 修改示例

以下示例将帮助您了解使用 Management Studio 和查询修改现有触发器的步骤。

在开始修改之前，我将向您展示 Management Studio 提供的可用选项。如下图所示，您可以在 Management Studio 中删除、重新创建、修改和创建触发器。

Available Options of Triggers

要使用 Management Studio 进行修改，请导航到包含触发器的表。接下来，选择要修改的触发器（例如 ExampleTR），右键单击它以打开上下文菜单。在此，选择 Modify 选项。

Modify Triggers in Management Studio

选择 Modify 选项后，一个新的查询窗口将打开，其中包含自动生成的代码。您可以根据需要进行修改。

使用 Alter Trigger 进行修改

使用 ALTER 语句修改现有触发器所涉及的步骤。为此，请单击 New query 并返回以下查询。

ALTER TRIGGER [dbo].[ExampleTR] 
   ON  [dbo].[EmployeeTR] 
   AFTER INSERT
AS 
BEGIN
 -- Modify as per Your requirement
END

SQL Server Delete Trigger 示例

以下示例将向您展示如何使用查询和 Management Studio (SSMS) 删除触发器。这里，我们展示如何使用 SSMS 删除它们。要使用 SSMS 删除，请右键单击要删除的触发器名称以打开上下文菜单。您可以单击 Delete 选项。

Delete or Drop Triggers in SQL Server

在这里，我们将向您展示如何使用 Drop TRIGGER 语句删除它。

DROP TRIGGER [dbo].[ExampleTR]