Nested Triggers in SQL Server
Nested Triggers in SQL Server
View all posts by Ben Richardson Latest posts by Ben Richardson (see all) Working with the SQL MIN function in SQL Server - May 12, 2022 SQL percentage calculation examples in SQL Server - January 19, 2022 Working with Power BI report themes - February 25, 2021
SQLShack
SQL Server training EspañolNested Triggers in SQL Server
March 27, 2019 by Ben Richardson 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) operations e.g. INSERT, UPDATE, DELETE or DDL (Data Definition Language) operations such as 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. 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. In this article we will see how nested triggers in SQL Server work. 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. 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.Creating Dummy Data
Before actually looking at an example of a nested trigger, let’s create some dummy data. Execute the following script: 1234567891011121314151617181920212223242526272829 CREATE DATABASE Showroom GO Use ShowroomCREATE 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. The Car table has five attributes: CarId, Name, Make, Model, Price and Type. Next, we added 12 dummy records to the Car table. The CarLog table has three columns: LogId, CarId and the CarName.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. 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. Let’s first write a Nested trigger in SQL that prevents the insertion of data into the CarLog table. 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. Execute the following script: 12345678 CREATE TRIGGER [dbo].[CarLOG_INSERT] ON [dbo].[CarLog]INSTEAD OF INSERTASBEGINPRINT('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. Let’s now try to insert a record into the CarLog table and see if our trigger actually works. Execute the following script: 12 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. 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: 1 SELECT * FROM CarLog In the output, you will see that the CarLog table is empty. Now, let’s create our second trigger on the Car table. This will execute after some records have been inserted into the Car table. The Nested trigger in SQL will insert records into the CarLog table. Run the following script: 1234567891011121314151617 CREATE TRIGGER [dbo].[CAR_INSERT] ON [dbo].[Car]AFTER INSERTASBEGIN 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. Now, let’s try and test our Car_INSERT trigger. Execute the following script to insert some data in Car table. 12 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. 1 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”. Now let’s see if the new record has been inserted into the CarLog table. Execute the following script: 1 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. 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. 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. 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. 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. The following script deletes the CarLog_INSERT nested trigger in SQL Server: 1 DROP TRIGGER [dbo].[CarLOG_INSERT] And the following script creates the updated version of the CarLog_INSERT trigger we discussed above: 1234567891011121314151617 CREATE TRIGGER [dbo].[CarLOG_INSERT] ON [dbo].[CarLog]INSTEAD OF INSERTASBEGIN 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. 12 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. Now let’s try to insert records via the Car table. 12 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. 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. 1 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. Note: For those interested there is a very useful article on 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 Author Recent Posts Ben RichardsonBen Richardson runs Acuity Training a leading provider of SQL training the UK. It offers a full range of SQL training from introductory courses through to advanced administration and data warehouse training – see here for more details. Acuity has offices in London and Guildford, Surrey. He also blogs occasionally on Acuity’s blogView all posts by Ben Richardson Latest posts by Ben Richardson (see all) Working with the SQL MIN function in SQL Server - May 12, 2022 SQL percentage calculation examples in SQL Server - January 19, 2022 Working with Power BI report themes - February 25, 2021