SQL Server INSTEAD OF INSERT 触发器

SQL Server 的 INSTEAD OF INSERT 触发器将在插入操作开始之前触发。INSTEAD OF INSERT 触发器可以创建在表和视图上。通常，我们会在视图上使用它们。对于这个 INSTEAD OF INSERT 触发器演示，我们将使用下面显示的表。

在这里，我们的任务是在此 Employee 表上创建 SQL Server INSTEAD OF INSERT 触发器。通过使用这个 INSTEAD OF INSERT 触发器，我们想要限制插入到 Employee 表的记录。同时，还要将记录插入到 Audit 表，并附带审计信息。

Source Table 0

我们的 Employee 表审计也是空的。

SQL Server INSTEAD OF INSERT 触发器示例

接下来，我们将使用 CREATE TRIGGER 语句在 Employee 表上创建 SQL Server 的 INSTEAD OF INSERT 触发器。

请记住，这个触发器将在 Employee 表的插入操作开始之前触发。一旦它完成将记录插入到 Employee Audit 表，它将开始将记录插入到 Employee 表。如果由于某种原因失败，它将不会插入到 Employee 表。

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

从下面的代码片段中，您可以看到我们正在使用INSERT INTO SELECT 语句来选择所有将要插入到 Employee 表的记录，然后

  • 我们将年收入小于等于 70000 的记录插入到 Audit 表，并且。
  • 将年收入大于 70000 的记录插入到 Employee 表。
CREATE TRIGGER InsteadOfINSERTTriggerExample on [EmployeeTable]
INSTEAD OF INSERT 
AS
BEGIN 
INSERT INTO [EmployeeTableAudit](
	[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
WHERE YearlyIncome <= 70000;
PRINT 'We Successfully Fired Our INSTEAD OF INSERT Triggers in SQL Server.';

INSERT INTO [EmployeeTable]( 
	[Name],
	[Education],
	[Occupation],
	[YearlyIncome],
	[Sales])
SELECT  Name,
	Education,
	Occupation,
	YearlyIncome,
	Sales
FROM INSERTED
WHERE YearlyIncome > 70000;
END
GO
INSTEAD OF INSERT Triggers in SQL Server 1

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

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

通过打开对象资源管理器，我将向您展示新创建的 SQL Server INSTEAD OF INSERT 触发器。接下来，转到 SQL Tutorial 数据库，展开 Employee 表，然后展开 Triggers 文件夹。

INSTEAD OF INSERT Triggers 2

为了演示 INSTEAD OF INSERT 触发器，我们将向 Employee 表插入 14 条记录，以检查 INSTEAD OF INSERT 触发器是否被触发。

INSERT INTO [EmployeeTable] ([Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales])
  SELECT [Firstname] + ' '[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales] 
  FROM [Employee]
INSTEAD OF INSERT Triggers 3

从上图可以看出，我们的 SQL Server INSTEAD OF INSERT 触发器已触发。并且，它没有将 14 条记录插入到 Employee 表，而是将 8 条记录插入到 Audit 表，将 6 条记录插入到 Employee 表。请使用以下 SQL 查询来检查 Employee 表中已插入的记录。

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

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

SELECT [Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[ServerName]
      ,[ServerInstanceName]
      ,[Insert Time]
  FROM [EmployeeTableAudit]
INSTEAD OF INSERT Triggers 5

您也可以尝试使用条件，例如：将年收入大于 7000 的记录插入到 Employee 表，否则，抛出一个错误（或消息）等。

评论已关闭。