本文介绍了SQL Server中的嵌套触发器，包括AFTER和INSTEAD OF触发器，以及如何通过@@NESTLEVEL控制触发器行为。示例展示了如何防止直接向CarLog表插入数据，以及允许Car_INSERT触发器进行插入。
sql 触发器嵌套条件
Nested Triggers in SQL Server are actions that automatically execute when a certain database operation is performed, for example, INSERT, DROP, UPDATE etc.
SQL Server中的嵌套触发器是在执行某些数据库操作（例如INSERT，DROP，UPDATE等）时自动执行的操作。
They execute as a result of DML (Data Manipulation Language) operations e.g. INSERT, UPDATE, DELETE or DDL (Data Definition Language) operations such as CREATE, ALTER, DROP.
它们作为DML（数据操作语言）操作（例如INSERT，UPDATE，DELETE或DDL（数据定义语言）操作，例如CREATE，ALTER，DROP）的结果而执行。
Nested Triggers in SQL Server can be broadly categorized into two types: AFTER triggers and INSTEAD OF triggers. AFTER triggers execute after a DML or DDL operation is performed. INSTEAD OF triggers execute in place of a DML or DDL operation.
SQL Server中的嵌套触发器可以大致分为两种类型：AFTER触发器和INSTEAD OF触发器。 执行DML或DDL操作后执行AFTER触发器。 INSTEAD OF触发器代替DML或DDL操作执行。
In addition to being triggered by DML and DDL operations, triggers in SQL Server can also be triggered by other triggers. This type trigger is called a nested trigger in SQL or a recursive trigger.
除了由DML和DDL操作触发之外，SQL Server中的触发器还可以由其他触发器触发。 这种类型的触发器在SQL中称为嵌套触发器或递归触发器 。
In this article we will see how nested triggers in SQL Server work.
在本文中，我们将看到SQL Server中的嵌套触发器如何工作。
Nested Triggers in SQL Server sometimes get a bad press. For those of you who are wondering if using triggers is a good idea, as with most things used in the right place and in the right way they work very well.
SQL Server中的嵌套触发器有时会受到负面影响。 对于那些想知道使用触发器是否是一个好主意的人来说，就像大多数事情在正确的地方以正确的方式使用一样，它们工作得很好。
Note: Used in the wrong place or in the wrong way (see Query optimization techniques in SQL Server: Database Design and Architecture article) they can lead to many problems as Are SQL Server database triggers evil? article lays out. If you’re unsure as always make sure that your database is properly backed up first.
注意： 在错误的地方或使用错误的方式（请参阅SQL Server中的查询优化技术：数据库设计和体系结构一文），它们会导致许多问题，因为SQL Server数据库是否会引发恶意？ 文章布局。 如果您始终不确定，请确保首先正确备份数据库。
创建虚拟数据 (Creating Dummy Data)
Before actually looking at an example of a nested trigger, let’s create some dummy data. Execute the following script:
在实际查看嵌套触发器的示例之前，让我们创建一些虚拟数据。 执行以下脚本：
CREATE DATABASE Showroom
GO
Use Showroom
CREATE TABLE Car
(
CarId int identity(1,1) primary key,
Name varchar(100),
Make varchar(100),
Model int ,
Price int ,
Type varchar(20)
)
insert into Car( Name, Make, Model , Price, Type)
VALUES ('Corrolla','Toyota',2015, 20000,'Sedan'),
('Civic','Honda',2018, 25000,'Sedan'),
('Passo','Toyota',2012, 18000,'Hatchback'),
('Land Cruiser','Toyota',2017, 40000,'SUV'),
('Corrolla','Toyota',2011, 17000,'Sedan')
CREATE TABLE CarLog
(
LogId int identity(1,1) primary key,
CarId int ,
CarName varchar(100),
)
In the script above, we create a database called Showroom with two tables: Car and CarLog.
在上面的脚本中，我们创建了一个名为Showroom的数据库，其中包含两个表：Car和CarLog。
The Car table has five attributes: CarId, Name, Make, Model, Price and Type.
Car表具有五个属性：CarId，名称，品牌，型号，价格和类型。
Next, we added 12 dummy records to the Car table.
接下来，我们在Car表中添加了12条虚拟记录。
The CarLog table has three columns: LogId, CarId and the CarName.
CarLog表具有三列：LogId，CarId和CarName。
了解SQL Server中的嵌套触发器 (Understanding Nested Triggers in SQL Server)
Suppose we want to ensure that no one can enter data directly into the CarLog table. Rather, that we want to be sure that when data is entered in the Car table, a subset of that data is entered into the CarLog table.
假设我们要确保没有人可以直接在CarLog表中输入数据。 相反，我们要确保在将数据输入Car表中时，将这些数据的一部分输入到CarLog表中。
To do this, we need to write two triggers. The first trigger will be specified on the CarLog table and it will prevent direct insertion of data into the table. The second trigger will be written on the Car table and will insert data into CarLog table after inserting data into the Car table.
为此，我们需要编写两个触发器。 将在CarLog表上指定第一个触发器，这将防止将数据直接插入表中。 第二个触发器将被写入Car表，并将数据插入Car表后将数据插入CarLog表。
Let’s first write a Nested trigger in SQL that prevents the insertion of data into the CarLog table.
首先，我们用SQL编写一个嵌套触发器，以防止将数据插入CarLog表中。
The trigger type will be INSTEAD OF because instead of inserting data into the table we want the trigger to display an error message to the user that direct insertion is not possible.
触发器类型为INSTEAD OF，因为我们希望触发器向用户显示一条错误消息，提示无法直接插入，而不是将数据插入表中。
Execute the following script:
执行以下脚本：
CREATE TRIGGER [dbo].[CarLOG_INSERT]
ON [dbo].[CarLog]
INSTEAD OF INSERT
AS
BEGIN
PRINT('DATA CANNOT BE INSERTED DIRECTLY IN CarLog TABLE')
END
In the script above, we create a triggered named “CarLog_INSERT” which is an INSTEAD OF type trigger. The trigger executes whenever someone tries to directly insert records into the CarLog table. The trigger simply displays a message to the user that direct insertion is not possible.
在上面的脚本中，我们创建一个名为“ CarLog_INSERT”的触发器，这是一个INSTEAD OF类型的触发器。 只要有人尝试将记录直接插入CarLog表中，触发器就会执行。 触发器仅向用户显示一条消息，提示无法直接插入。
Let’s now try to insert a record into the CarLog table and see if our trigger actually works. Execute the following script:
现在，让我们尝试将记录插入CarLog表中，看看我们的触发器是否真正起作用。 执行以下脚本：
INSERT INTO CarLog( CarId , CarName)
VALUES (2, 'Civic')
In the output, you will see the following message:
在输出中，您将看到以下消息：
The trigger has executed and instead of inserting a record into the CarLog table, it has displayed the message that direct insertion is not possible.
触发器已执行，并且未将记录插入CarLog表中，而是显示了消息，提示无法直接插入。
Let’s try to SELECT all the records from the CarLog table to verify that no record has been inserted into the CarLog table. Run the following script:
让我们尝试从CarLog表中选择所有记录，以验证没有记录插入CarLog表中。 运行以下脚本：
SELECT * FROM CarLog
In the output, you will see that the CarLog table is empty.
在输出中，您将看到CarLog表为空。
Now, let’s create our second trigger on the Car table. This will execute after some records have been inserted into the Car table.
现在，让我们在Car表上创建第二个触发器。 这将在将某些记录插入Car表后执行。
The Nested trigger in SQL will insert records into the CarLog table. Run the following script:
SQL中的嵌套触发器会将记录插入CarLog表中。 运行以下脚本：
CREATE TRIGGER [dbo].[CAR_INSERT]
ON [dbo].[Car]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @car_id INT, @car_name VARCHAR(50)
SELECT @car_id = INSERTED.CarId, @car_name = INSERTED.name
FROM INSERTED
INSERT INTO CarLog
VALUES(@car_id, @car_name)
END
The Car_INSERT trigger is of the AFTER INSERT type, and inserts records into the CarLog table after inserting records into the Car table.
Car_INSERT触发器为AFTER INSERT类型，在将记录插入Car表后将记录插入CarLog表。
Now, let’s try and test our Car_INSERT trigger.
现在，让我们尝试测试我们的Car_INSERT触发器。
Execute the following script to insert some data in Car table.
执行以下脚本以在Car表中插入一些数据。
insert into Car( Name, Make, Model , Price, Type)
VALUES ('Mustang','Ford',2014, 25000,'Sedan')
When you execute the script above, you will again see the following message output:
当您执行上面的脚本时，您将再次看到以下消息输出：
Let’s see if our data has been inserted into both the Car and CarLog table, or not. Let’s first we need to select the Car table records.
让我们看看我们的数据是否已经插入到Car和CarLog表中。 首先，我们需要选择Car表记录。
SELECT * FROM Car
The output looks like this:
输出看起来像这样：
In the output, at the bottom, you can see the newly inserted record where the name of the car is “Mustang”.
在输出的底部，您可以看到新插入的记录，其中汽车的名称为“ Mustang”。
Now let’s see if the new record has been inserted into the CarLog table. Execute the following script:
现在，让我们看看新记录是否已插入CarLog表中。 执行以下脚本：
SELECT * FROM CarLog
Output:
输出：
You can see an empty table in the output. This means that the record was inserted into the Car table, then the Car_INSERT nested trigger in SQL executed which tried to insert the data into the CarLog table. However, when the Car_INSERT trigger tried to insert data into the CarLog table, the nested CarLog_INSERT trigger also executed which prevented data from being inserted into the CarLog table. This shows how a trigger can be used to make another trigger to execute.
您可以在输出中看到一个空表。 这意味着该记录已插入Car表中，然后执行了SQL中的Car_INSERT嵌套触发器，该触发器试图将数据插入CarLog表中。 但是，当Car_INSERT触发器尝试将数据插入CarLog表时，嵌套的CarLog_INSERT触发器也会执行，这会阻止将数据插入CarLog表。 这显示了如何使用触发器来使另一个触发器执行。
Coming back to our use case. We want to prevent direct insertion of data into the CarLog table. We want data to be inserted via the Car_INSERT trigger. However, currently the CarLog_INSERT trigger is preventing both direct insertion and the insertion of data via the Car_INSERT trigger.
回到我们的用例。 我们要防止将数据直接插入CarLog表中。 我们希望通过Car_INSERT触发器插入数据。 但是，当前CarLog_INSERT触发器既阻止直接插入，也阻止通过Car_INSERT触发器插入数据。
We need to update the CarLog_INSERT trigger so that when someone tries to directly insert data into the CarLog table, the insertion is prevented, but when the insertion is performed via the Car_INSERT trigger, it is allowed.
我们需要更新CarLog_INSERT触发器，以便当有人尝试将数据直接插入CarLog表时，将阻止插入，但是当通过Car_INSERT触发器执行插入时，将允许插入。
Before we update our trigger we need to know that each trigger is assigned an integer value called @@NESTLEVEL depending upon the source of the trigger’s execution, If the trigger is executed directly, the value for the @@NESTLEVEL for that trigger is set to 1. However, if a trigger is triggered by another trigger, the @@NESTLEVEL value is set to 2. Similarly, if the trigger is executed as a result of another trigger which is executed as a result of another trigger, the @@NESTLEVEL of the innermost trigger will be set to 3. The maximum number of nested triggers allowed by SQL Server is 32.
在更新触发器之前，我们需要知道，根据触发器的执行源，为每个触发器分配了一个称为@@ NESTLEVEL的整数值。如果直接执行该触发器，则将该触发器的@@ NESTLEVEL的值设置为1.但是，如果某个触发器由另一个触发器触发，则@@ NESTLEVEL值将设置为2。类似地，如果该触发器是由于另一个触发器而执行的，而另一个触发器是由于另一个触发器而执行的，则@@ NESTLEVEL最里面的触发器的最大数量将设置为3。SQLServer允许的嵌套触发器的最大数量为32。
Now that we understand the @@NESTLEVEL value, we will update the CarLog_INSERT trigger so that when it has a @@NESTLEVEL value of 1 (direct insertion), the record will not be inserted into the CarLog table, but so that if the @@NESTLEVEL value is not equal to 1 ( insertion through another trigger which gives an @@NESTLEVEL of 2), the record will be inserted.
现在我们了解了@@ NESTLEVEL值，我们将更新CarLog_INSERT触发器，以便当它的@@ NESTLEVEL值为1（直接插入）时，记录不会插入到CarLog表中，但是如果@ @NESTLEVEL值不等于1（通过另一个触发器提供的@@ NESTLEVEL为2），将插入记录。
The following script deletes the CarLog_INSERT nested trigger in SQL Server:
以下脚本在SQL Server中删除CarLog_INSERT嵌套触发器：
DROP TRIGGER [dbo].[CarLOG_INSERT]
And the following script creates the updated version of the CarLog_INSERT trigger we discussed above:
以下脚本创建了我们上面讨论的CarLog_INSERT触发器的更新版本：
CREATE TRIGGER [dbo].[CarLOG_INSERT] ON [dbo].[CarLog]
INSTEAD OF INSERT
AS
BEGIN
IF @@NESTLEVEL = 1
PRINT('DATA CANNOT BE INSERTED DIRECTLY IN CarLog TABLE')
ELSE
BEGIN
DECLARE @car_id INT, @car_name VARCHAR(50)
SELECT @car_id = INSERTED.CarId, @car_name = INSERTED.CarName
FROM INSERTED
INSERT INTO CarLog
VALUES(@car_id, @car_name)
END
END
Now let’s first try to insert a record directly into the CarLog table.
现在，让我们首先尝试将记录直接插入CarLog表中。
INSERT INTO CarLog( CarId , CarName)
VALUES (2, 'Civic')
If you SELECT all the records from the CarLog table, you will see that no record has inserted since direct insertion is prevented by the CarLog_INSERT trigger.
如果从CarLog表中选择所有记录，您将看到没有记录插入，因为CarLog_INSERT触发器阻止了直接插入。
Now let’s try to insert records via the Car table.
现在，让我们尝试通过Car表插入记录。
insert into Car( Name, Make, Model , Price, Type)
VALUES ('Clio','Renault',2012, 5000,'Sedan')
When you insert the above record into the Car table, the Car_INSERT trigger executes and will try to insert a record into the CarLog table. This will in turn trigger the nested CarLog_INSERT trigger.
当您将以上记录插入Car表中时，Car_INSERT触发器将执行，并将尝试将记录插入CarLog表中。 这将依次触发嵌套的CarLog_INSERT触发器。
Inside the CarLog_INSERT trigger the @@NESTLEVEL value of the nested trigger will be checked and since the insertion is not direct, the record will be inserted into the CarLog table as well. You can verify this by issuing the following command.
在CarLog_INSERT触发器内部，将检查嵌套触发器的@@ NESTLEVEL值，由于插入不是直接的，因此记录也将插入到CarLog表中。 您可以通过发出以下命令来验证这一点。
SELECT * FROM CarLog
In the output, you will see the newly inserted record:
在输出中，您将看到新插入的记录：
结论 (Conclusion)
Nested triggers in SQL Server (also known as recursive triggers) are triggers that are fired as a result of the execution of other triggers. In this article we saw how nested triggers execute. We also saw how we can make a nested trigger fire only when it is executed indirectly by other triggers.
SQL Server中的嵌套触发器（也称为递归触发器）是由于执行其他触发器而触发的触发器。 在本文中，我们看到了嵌套触发器如何执行。 我们还看到了仅当嵌套触发器由其他触发器间接执行时，如何才能使嵌套触发器触发。
Note: 注意： Disabling Triggers for a specific session.为特定会话禁用触发器 。
本的其他精彩文章 (Other great articles from Ben)
| Understanding SQL Server query plan cache |
| Understanding the GUID data type in SQL Server |
| Nested Triggers in SQL Server |
| 了解SQL Server查询计划缓存 |
| 了解SQL Server中的GUID数据类型 |
| SQL Server中的嵌套触发器 |
翻译自: https://www.sqlshack.com/nested-triggers-in-sql-server-6/
sql 触发器嵌套条件
扫一扫
1283
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
