sql触发器嵌套条件_SQLServer中的嵌套触发器
sql 触发器嵌套条件
Nested Triggers in SQL Server are actions that automatically execute when a certain database operation is performed, for example, INSERT, DROP, UPDATE etc.
They execute as a result of DML (Data Manipulation Language) 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 article) they can lead to many problems as article lays out. If you’re unsure as always make sure that your database is first.
注意：在错误的地⽅或使⽤错误的⽅式（请参阅⽂），它们会导致许多问题，因为⽂章布局。如果您始终不确定，请确保⾸先数据库。
创建虚拟数据 (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.
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
科鲁兹大包围车载mp4>法比亚晶锐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。
发布评论