SQL Server 中的 AFTER DELETE 触发器

SQL Server 的 AFTER Delete 触发器将在表上的 Delete 操作完成后触发。 SQL After DELETE 触发器不支持在视图上使用。在本示例中，我们将使用下面显示的表来演示 SQL Server AFTER Delete 触发器。

在此，我们的任务是在此 Employee 表上创建 SQL Server AFTER DELETE TRIGGER。通过使用此 AFTER Delete Trigger，我们希望将从 Employee 表中删除的记录插入到 Employee Audit 表中。

我们的 Employee 表 Audit 是空的。

SQL Server 中的 AFTER DELETE 触发器示例

在此示例中，我们将向您展示如何在 SQL Server 中创建 After delete Triggers。这里，我们使用 CREATE TRIGGER 语句在 Employee 表上创建触发器。

提示：您可以参考 SQL Server 中的 TRIGGERS、AFTER INSERT TRIGGERS、Views 和 AFTER UPDATE TRIGGERS 文章。

请记住，After delete trigger 将在 Employee 表上的 Delete 操作完成后触发。一旦它完成从 Employee 表中删除记录，它将开始从 Employee audit 表中插入/删除。如果它未能从 Employee 表中删除，那么它将不会插入到 Audit 表中。

-- Example for After DELETE Triggers in SQL Server

CREATE TRIGGER AfterDELETETrigger on [EmployeeTable]
FOR DELETE 
AS DECLARE @EmpID INT,
           @EmpName VARCHAR(50),
	   @EmpEducation VARCHAR(50),
	   @EmpOccupation VARCHAR(50),
	   @EmpYearlyIncome DECIMAL (10, 2), 
	   @EmpSales DECIMAL (10, 2);

SELECT @EmpID = del.ID FROM DELETED del;
SELECT @EmpName = del.Name FROM DELETED del;
SELECT @EmpEducation = del.Education FROM DELETED del;
SELECT @EmpOccupation = del.Occupation FROM DELETED del;
SELECT @EmpYearlyIncome = del.YearlyIncome FROM DELETED del;
SELECT @EmpSales = del.Sales FROM DELETED del;
  
INSERT INTO [EmployeeAuditTable]( 
       ID
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[ServerName]
      ,[ServerInstanceName]
      ,[Deleted Time])
VALUES (@EmpID,
        @EmpName,
	@EmpEducation,
	@EmpOccupation,
	@EmpYearlyIncome,
	@EmpSales,
	CAST( SERVERPROPERTY('MachineName') AS VARCHAR(50)), 
	CAST( SERVERPROPERTY('ServerName') AS VARCHAR(50)), 
	GETDATE());
PRINT 'We Successfully Fired the AFTER DELETE Triggers in SQL Server.'
GO

首先，我们使用 DECLARE 语句声明了所需的变量。

DECLARE @EmpID INT,
        @EmpName VARCHAR(50),
        @EmpEducation VARCHAR(50),
	@EmpOccupation VARCHAR(50),
	@EmpYearlyIncome DECIMAL (10, 2), 
	@EmpSales DECIMAL (10, 2);

接下来，我们使用 SELECT Statement 选择已删除的记录。以下语句将仅从已删除的行中选择一条记录。

SELECT @EmpID = del.ID FROM DELETED del;
SELECT @EmpName = del.Name FROM DELETED del;
SELECT @EmpEducation = del.Education FROM DELETED del;
SELECT @EmpOccupation = del.Occupation FROM DELETED del;
SELECT @EmpYearlyIncome = del.YearlyIncome FROM DELETED del;
SELECT @EmpSales = del.Sales FROM DELETED del;

接下来，我们使用 INSERT 语句将选定的值插入到 Employee Audit 表中。这里，以下语句将返回机器名称和服务器名称。此信息可能有助于审计。

SERVERPROPERTY('MachineName'), 
SERVERPROPERTY('ServerName')

让我通过对象资源管理器向您展示新创建的 SQL Server AFTER Delete Trigger。为此，请转到 SQL Tutorial Database -> 找到并展开 Employee Table -> 然后展开 Triggers 文件夹。

为了演示目的，我们将删除 Occupation = ‘Clerical’ 的记录，以检查 After delete Trigger 是否被触发。

-- SQL AFTER DELETE Trigger Example

DELETE FROM [EmployeeTable]
	WHERE [Occupation] = N'Clerical'

如您所见，我们的 SQL Server AFTER Delete Triggers 被触发了，并且还将一条记录插入了 Audit Table。请使用以下 SQL 查询检查 Employee 表。

-- SQL AFTER DELETE Trigger Example
SELECT [ID]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeTable]

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

-- SQL AFTER DELETE Trigger Example
SELECT [ID]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[ServerName]
      ,[ServerInstanceName]
      ,[Deleted Time]
  FROM [EmployeeAuditTable]

从上图可以看出，我们的 employee Audit Table 返回了一条记录。因为在我们的触发器定义中，我们为每个删除操作只选择了一条记录。

SQL Server 中的 AFTER DELETE 触发器示例 2

此示例演示了如何使用 After delete Triggers 将所有已删除的记录插入到 employee audit table（触发的表）中。在这里，我们将修改我们在上一个示例中创建的触发器。

-- Example for After DELETE Triggers in SQL Server

CREATE TRIGGER AfterDELETETrigger on [EmployeeTable]
FOR DELETE 
AS 
INSERT INTO [EmployeeAuditTable]( 
       ID
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[ServerName]
      ,[ServerInstanceName]
      ,[Deleted Time])
  SELECT ID
	,[Name]
	,[Education]
	,[Occupation]
	,[YearlyIncome]
	,[Sales]
	,CAST( SERVERPROPERTY('MachineName') AS VARCHAR(50)) 
	,CAST( SERVERPROPERTY('ServerName') AS VARCHAR(50)) 
	,GETDATE()
FROM DELETED;
PRINT 'We Successfully Fired the AFTER DELETE Triggers in SQL Server.'
GO

如上所示，我们使用 INSERT INTO SELECT Statement 来选择从 Employee 表中删除的所有记录。然后，我们将这些记录插入到 Employee Audit 表中。

接下来，让我删除 Employee 表中 Occupation 为 Management 或 Education 为 High School 的记录。

-- SQL Server AFTER DELETE Trigger Example

DELETE FROM [EmployeeTable]
	WHERE [Occupation] = N'Management' OR
		[Education] = N'High School'

从上图可以看出，after delete trigger 已被触发。并且还将所有记录插入到 audit table。让我们看看 Employee 表。

接下来，用 Employee Audit 表进行检查。

从上图可以看出，触发器已插入所有记录。