SQL Server INSTEAD OF UPDATE 触发器示例

SQL INSTEAD OF UPDATE 触发器将在执行开始前触发。因此，您可以使用此 SQL Server INSTEAD OF UPDATE 触发器来为标识列传递值或更新不同的表等。

对于此 SQL INSTEAD OF UPDATE 触发器演示，我们将使用下面显示的表。在这里，我们的任务是在此 Employee 表上创建 SQL Server INSTEAD OF UPDATE TRIGGER。并通过使用此 Instead of UPDATE Trigger，我们希望限制记录更新。

我们的 Employee 表审计也保存了相同的 14 条记录，以及服务器名称、服务器实例名称和插入时间（审计信息）。

SQL Server INSTEAD OF UPDATE 触发器示例

SQL Instead of Update Triggers 可以创建在表和视图上。通常，我们在视图上使用这些触发器。在此示例中，我们将展示如何在 SQL Server 中创建 Instead of update Triggers。例如，如果您想阻止用户更新原始表中的记录。并且您希望将这些更新的日志保存在另一个历史表中，请使用此触发器。

提示：您可以参考 SQL Server 中的 视图、触发器、INSTEAD OF INSERT 触发器 和 INSTEAD OF DELETE 触发器 文章。

在这里，我们将使用 CREATE TRIGGER 语句在 Employee 表上创建 SQL Server 的 Instead of UPDATE Triggers。从下面的代码片段中，您可以看到我们正在使用 INSERT INTO SELECT 语句 来选择所有插入到 Employee 表的记录。然后我们将这些记录插入到 Audit 表。

这意味着，当用户更新任何 Employee 表记录时，触发器会将这些记录插入到审计表中，并保持 Employee 表不变。

-- Example for INSTEAD OF UPDATE Triggers in SQL Server

CREATE TRIGGER InsteadOfUPDATETriggerExample on [EmployeeTable]
INSTEAD OF UPDATE 
AS DECLARE @ActionPeformed VARCHAR(50)

IF UPDATE(YearlyIncome)
BEGIN
     SET @ActionPeformed = 'Updated Yearly Income'
END
ELSE BEGIN
      SET @ActionPeformed = 'Updated Sales'
END

INSERT INTO [EmployeeTableAudit]( 
       [ID]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[Update Time]
     ,[ActionPerformed])
SELECT ID,
	Name,
	Education,
	Occupation,
	YearlyIncome,
	Sales,
	GETDATE(),
	@ActionPeformed
FROM INSERTED;
PRINT 'We Successfully Fired Our First INSTEAD OF UPDATE Triggers in SQL Server.'
GO

我将向您展示新创建的触发器，方法是打开对象资源管理器 -> 转到数据库 -> 然后展开 Employee 表 -> 然后展开 Triggers 文件夹。

为了演示，我们将更新 Employee 表中 occupation = Professional 的所有记录的 Yearly Income 为 123,456，Sales 为 5000。

-- SQL INSTEAD OF UPDATE Triggers Example

UPDATE [EmployeeTable]
	SET [YearlyIncome] = 123456, [Sales] = 5000
	WHERE [Occupation] = N'Professional'

从上面的屏幕截图中，您可以看到我们的触发器已被触发。并且，不是将 4 条记录插入 Employee Audit 表。请使用以下查询来检查 Employee 表中的插入记录。

-- SQL INSTEAD OF UPDATE Triggers Example

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

尽管我们更新了 Employee 表，但正如您从上面的屏幕截图中看到的，我们的 Employee 表保持不变。接下来，使用以下查询检查 Employee 表审计中的记录。

-- SQL Server INSTEAD OF UPDATE Triggers Example

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

这里，您可以看到触发器已将 4 条新记录插入 Employee audit 表。

SQL Server INSTEAD OF UPDATE 触发器示例 2

让我们看看如何使用 SQL Server 的 Instead of Update Triggers 来更新审计表（触发的表）中的所有记录。对于此 SQL Server Instead of UPDATE Triggers 示例，我们将使用 MERGE 语句。

CREATE TRIGGER InsteadOfUPDATETriggerExample on [EmployeeTable]
INSTEAD OF UPDATE 
AS DECLARE @ActionPeformed VARCHAR(50)

IF UPDATE(YearlyIncome)
BEGIN
     SET @ActionPeformed = 'Updated Yearly Income'
END
ELSE BEGIN
      SET @ActionPeformed = 'Updated Sales'
END

MERGE [EmployeeTableAudit] AS AuditTab
USING (SELECT * FROM INSERTED) AS Emp
ON AuditTab.ID = emp.ID
WHEN MATCHED THEN
UPDATE SET AuditTab.[Name] = Emp.Name, 
         AuditTab.[Education] = Emp.Education, 
	 AuditTab.[Occupation] = Emp.Occupation,
	 AuditTab.[YearlyIncome] = Emp.YearlyIncome, 
	 AuditTab.[Sales] = Emp.Sales, 
	 AuditTab.[Update Time] = GETDATE(), 
	 AuditTab.[ActionPerformed] = @ActionPeformed;
PRINT 'We Successfully Fired Our Second INSTEAD OF UPDATE Triggers in SQL Server.'
GO

接下来，我将执行对 employee 表的更新。

-- SQL INSTEAD OF UPDATE Triggers Example

UPDATE [EmployeeTable]
	SET [YearlyIncome] = 111111, 
	    [Sales] = 7777
	WHERE [Occupation] = N'Clerical'

我们不必检查 Employee 表，因为我们都知道该表中不会有任何更新。接下来，检查 Employee Audit 表。

从上面的屏幕截图中，您可以看到触发器已更新所有记录。

SQL Server INSTEAD OF UPDATE 触发器示例 3

此示例向您展示如何使用 SQL Server 的 Instead Of Update Triggers 来更新 Employee 表中的所有行。为此，我们使用了 INNER JOIN。

-- Example for INSTEAD OF UPDATE Triggers in SQL Server

CREATE TRIGGER InsteadOfUPDATETriggerExample on [EmployeeTable]
INSTEAD OF UPDATE 
AS 

UPDATE [EmployeeTable] 
 SET [EmployeeTable].[Name] = 'Tutorial Gateway', 
     [EmployeeTable].[Education] = ins.Education,
     [EmployeeTable].[Occupation] = ins.Occupation,
     [EmployeeTable].[YearlyIncome] = ins.YearlyIncome,
     [EmployeeTable].[Sales] = 55555
 FROM [EmployeeTable] 
 INNER JOIN INSERTED AS ins
 ON [EmployeeTable].ID = ins.ID 

PRINT 'We Successfully Fired Our Third INSTEAD OF UPDATE Triggers.'
GO

以下语句将名称设置为“Tutorial gateway”。

SET [EmployeeTable].[Name] = 'Tutorial Gateway',

下一个语句将 Sales 金额设置为 55,555。

 [EmployeeTable].[Sales] = 55555

这意味着，无论您将值传递给 name 和 Sales 列，SQL Server 的 Instead of Update Trigger 都会插入“Tutorial gateway”和 55,555。或者可以说，触发器将覆盖这些值。

接下来，我将执行对 employee 表的更新。

UPDATE [EmployeeTable]
	SET [YearlyIncome] = 999999, 
	    [Sales] = 88886
	WHERE [Occupation] = N'Management'

从上面的屏幕截图中，我们的 SQL instead of update 触发器已触发。并且也更新了 employee 表中的所有行。让我们看看 Employee 表。