Post
Ask Question

Triggers In SQL Server

Introduction

In this blog, you will learn about Triggers in SQL Server and I will explain the concept with an example in a simple way. I hope this is very useful for beginners and intermediates to help them understand the basic concept.

What is a Trigger?

A trigger is a compiled special kind of stored procedure that executes in response to a certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers. Triggers execute when a user tries to modify data through a DML event. DML events are INSERT, UPDATE, or DELETE statements on a table. A trigger is triggered automatically when an associated DML statement is executed. The Triggers can be defined on the table, view with which the event is associated.

When we use the triggers or advantages of Triggers

Triggers can be written for the following purposes,

  1. To generate some derived column values automatically
  2. To enforce referential integrity
  3. To do event logging and store information on table access
  4. To audit
  5. To perform synchronous replication of tables
  6. To impose security authorizations
  7. To prevent the invalid transactions

In SQL Server, There are three types of triggers,

  1. DDL Trigger
  2. DML Trigger
  3. Logon Trigger

DDL Trigger

The DDL Trigger will execute whenever you will do insert, update, delete and drop operations.

DML Trigger

DML Trigger is fired automatically in the response of DML statements INSERT, UPDATE, DELETE statements. In this blog, we will discuss DML Triggers with examples. Let’s say, the price of product changes constantly, it is important to maintain the history of the prices of products. Here, we are going to create two tables first Product and second Product_history.

  1. CREATE TABLE Product  
  2. (  
  3.    product_id int primary key identity,  
  4.    product_name varchar(40),  
  5.    unit_price money  
  6. );  
Now, insert the some records in the below table. 
  1. INSERT INTO Product values('L538',7000)  
  2. INSERT INTO Product values('L550',7000)  
  3. INSERT INTO Product values('L551',7000)  
  4. SELECT * FROM product  

Output

 
  1. CREATE TABLE Product_histroy  
  2. (  
  3.    Id int primary key identity,  
  4.    Product_histroy varchar(200),  
  5. );  
  6. SELECT * FROM Product_histroy  
We are going to create a trigger on the insert, update, delete DML actions and the product price history. It should be updated when the price of the product is inserted, updated and deleted in the product table.

Syntax

  1. CREATE TRIGGER Trigger_Name  
  2. ON Table_Name  
  3. {INSERT [OR] | UPDATE [OR] | DELETE}  
  4. AS  
  5. Begin  
  6.    --Declaration Part  
  7.    --Select Part  
  8.    --Executable-Code  
  9.    --EXCEPTION  
  10.    --Exception-Handling-Code  
  11. END;  

DML Trigger can be classified into two types,

  1. After Trigger
  2. Instead Of trigger

After triggers fire after triggering action

The insert, update, delete statement causes the after trigger to fire after their respective statement to complete execution. After triggers are invoked after DML (insert, update and delete) operations. They are not supported for views. And after, the trigger is also divided into the following 3 parts,

  1. After Insert
  2. After Delete
  3. After Update 

After INSERT

Let’s have look at after insert trigger below.

Syntax

  1. CREATE TRIGGER UTRG_TriggerName  
  2. ON  
  3. TableName  
  4. FOR INSERT  
  5. AS BEGIN  
  6.    --Declaration Part  
  7.    --Select Part  
  8.    --Executable-Code  
  9.    --EXCEPTION  
  10.    --Exception-Handling-Code  
  11. END  

We are going to write the trigger on Product and whenever a new model gets launched or the old model's price is updated or the old vehicle model is deleted from the table then it has some record in another table ‘Product_History’. We have used ‘Product_History’ table for the same purpose.

Example

  1. CREATE TRIGGER UTRG_Product_Insert  
  2. ON  
  3. Product  
  4. FOR INSERT  
  5. AS BEGIN  
  6. DECLARE @product_id int, @product_name varchar(40), @unit_price money  
  7. SELECT @product_id = product_id, @product_name = product_name, @unit_price = unit_price FROM inserted  
  8. INSERT INTO Product_histroy VALUES('New product with Id ' + cast (@product_id As varchar(40)) +' product_name '+ @product_name + 'on date ' + cast (getdate() As varchar(40)))  
  9. END  

Output

 

In the above trigger, we have selected the column value from the ‘inserted’ table, this table is nothing but a temporary table, whenever you do any insert operation intermittent in trigger only we can select the values and do the required operation as per your requirement. Execute the below code to check if the records are affected or not in the below tables.

  1. select * from Product  
  2. select * from Product_histroy  

Output

 

If you wanted to do some changes in the above trigger use ALTER command and do the respective changes as per the project requirement.

Alter Trigger

  1. ALTER TRIGGER UTRG_Product_Insert  
  2. ON  
  3. Product  
  4. FOR INSERT  
  5. AS BEGIN  
  6. DECLARE @product_id int, @product_name varchar(40), @unit_price money  
  7. SELECT @product_id = product_id, @product_name = product_name, @unit_price = unit_price FROM inserted  
  8. INSERT INTO Product_histroy VALUES('New product with Id ' + cast (@product_id As varchar(40)) +' product_name '+ @product_name + ' on date ' + cast (getdate() As varchar(40))+ ' cost is ' + cast( @unit_price as varchar(40)))  
  9. END  
Here we are going to insert one record and execute the below code,
  1. INSERT INTO Product VALUES('L575',14000)  
  2. select * from Product  
  3. select * from Product_histroy  

Output

 

After Update

An after update trigger is called immediately whenever any type of update operation is done on a table. Now we are going to create update trigger for update operation like below.

If you want to print any message in trigger then you can use PRINT command.

Syntax

  1. CREATE TRIGGER UTRG_Product_Update  
  2. ON  
  3. Product  
  4. FOR UPDATE  
  5. AS BEGIN  
  6. --Declaration Part  
  7. --Select Part  
  8. --Executable-Code  
  9. --EXCEPTION  
  10. --Exception-Handling-Code  
  11. END  

Example

  1. CREATE TRIGGER UTRG_Product_Update  
  2. ON  
  3. Product  
  4. FOR UPDATE  
  5. AS BEGIN  
  6. DECLARE @product_id int  
  7. DECLARE @product_name varchar(40)  
  8. DECLARE @unit_price varchar(40)  
  9. SELECT @product_id = product_id FROM deleted  
  10. SELECT @product_name = product_name FROM deleted  
  11. SELECT @unit_price = unit_price FROM deleted  
  12. INSERT INTO Product_histroy VALUES('Product with Id ' + cast (@product_id As varchar(40)) +' cost '+ cast (@unit_price As varchar(40)) +' is updated on date '+cast(getdate() as varchar(40)))  
  13. END  
Example
  1. ALTER TRIGGER UTRG_Product_Update  
  2. ON  
  3. Product  
  4. FOR UPDATE  
  5. AS BEGIN  
  6. DECLARE @product_id int  
  7. DECLARE @product_name varchar(40)  
  8. DECLARE @unit_price varchar(40)  
  9. SELECT @product_id = product_id FROM deleted  
  10. SELECT @product_name = product_name FROM deleted  
  11. SELECT @unit_price = unit_price FROM deleted  
  12. INSERT INTO Product_histroy VALUES('Product with Id ' + cast (@product_id As varchar(40)) +' cost '+ cast (@unit_price As varchar(40)) +' is updated on date '+cast(getdate() as varchar(40)))  
  13. END  
Execute the below snippet code to update the price and name of the product.
  1. select * from Product  
  2. update Product  
  3. set product_name = 'L562', unit_price = 12500  
  4. where product_id = 7  

Output (Product table)

 

Execute the below code

  1. select * from Product  
  2. select * from Product_histroy  
  3. update Product  
  4. set product_name = 'L562', unit_price = 12500  
  5. where product_id = 7  

 Output(Product_histroy)

 

After Delete

There are some situations where we need to delete the vehicle model form product table and need deleted model information in the product history table regarding which record has been deleted on which date. We are here going to create Product_Delete trigger.

Syntax

  1. CREATE TRIGGER UTRG_TriggerName  
  2. ON  
  3. TableName  
  4. FOR DELETE  
  5. AS BEGIN  
  6. --Declaration Part  
  7. --Select Part  
  8. --Executable-Code  
  9. --EXCEPTION  
  10. --Exception-Handling-Code  
  11. END  

Example

  1. CREATE TRIGGER UTRG_Product_Delete  
  2. ON  
  3. Product  
  4. FOR DELETE  
  5. AS BEGIN  
  6. DECLARE @product_id int  
  7. SELECT @product_id = product_id FROM deleted  
  8. INSERT INTO Product_histroy VALUES('An existing product with Id ' + cast (@product_id As varchar(40)) +' cost is deleted')  
  9. END  
We have the below records.
  1. select * from Product  
Output

 

Now we are here going to delete the record for executing the below statement.

  1. DELETE FROM Product WHERE product_id =10  

Output Messages

 

One record is deleted from the table  -- see the output which records what's been deleted and added in the product history table.

Output 

 

Instead of Triggers

Now, let us see where it fires instead of triggering an action. The insert, update, and delete statements cause an error instead of statement execution. We can’t actually insert the row,  instead we have to instruct another way to insert the record.

Types of Instead Of Triggers

  1. Instead Of Insert
  2. Instead Of Update
  3. Instead Of Delete

Instead of triggers fire instead of triggering action

For example, we are going to add one more table called customer who booked or purchased the car. Execute the below script to create and add some records in it.

  1. CREATE TABLE [dbo].[Customer](  
  2. [Id] [int] NOT NULL,  
  3. [Name] [varchar](50) NULL,  
  4. [Balance] [money] NULL,  
  5.  CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED  
  6. (  
  7. [Id] ASC  
  8. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  9. ) ON [PRIMARY]  
Execute the below insert script in table,
  1. USE [ExampleDB]  
  2. GO  
  3. INSERT [dbo].[Customer] ([Id], [Name], [Balance]) VALUES (1, N'Shrimant', 25000.0000)  
  4. INSERT [dbo].[Customer] ([Id], [Name], [Balance]) VALUES (2, N'Arun', 25000.0000)  
  5. INSERT [dbo].[Customer] ([Id], [Name], [Balance]) VALUES (3, N'Kishor', 35000.0000)  
  6. INSERT [dbo].[Customer] ([Id], [Name], [Balance]) VALUES (4, N'Madhav', 45000.0000)  
  7. INSERT [dbo].[Customer] ([Id], [Name], [Balance]) VALUES (5, N'Jitendra', 35000.0000)  
We will have a look at the syntax and implementation of the   instead of trigger now. As we know, insert statements affects multiple based tables, then SQL server through the error. We will see this scenario with an example. We have two tables; the first one is product and the second one is customer.
  1. select * from [Customer]  
  2. select * from [Product]  

Product

 

Customer

 

View 

Now we are going to create the view to check if the insert statement affects multiple based tables then SQL server through the error. Let’s say from the customer table we want id, name, balance; and from the product table, we want product_Name to create the view like below.

  1. CREATE VIEW UV_CustomerBooking  
  2.  AS  
  3.  SELECT c.Id, c.Name, c.Balance, p.product_name FROM Customer c  
  4.  INNER JOIN  
  5.  Product p on c.ProductId = p.product_id  

Select View

  1. SELECT * FROM UV_CustomerBooking  

Output

 

 
Now we are going to insert one more record in view
  1. INSERT INTO UV_CustomerBooking VALUES('Samir',55000,'L540')  

Output

 
Basically, instead of inserting records on multiple tables, in this situation, we can use Instead of triggers. Now we are going to create instead of trigger in view.
  1. CREATE TRIGGER UTrig_CustomerBooking  
  2.  ON UV_CustomerBooking  
  3.  INSTEAD OF INSERT  
  4.  AS  
  5.  BEGIN  
  6. SELECT * FROM Inserted  
  7. SELECT * FROM Deleted  
  8.  END  
In above ‘UTrig_CustomerBooking’, Inserted table contains newly added data.

Deleted table contains old data. But here we didn’t delete any row, so the then output  is like below.

Output

 
 
As it seems in output, from the inserted magic table we are selecting Id, Name, Balance and ‘L540’ vehicle model or product name. We have to query the product name on the product table and get the product id like below.
  1. ALTER TRIGGER UTrig_CustomerBooking  
  2.  ON UV_CustomerBooking  
  3.  INSTEAD OF INSERT  
  4.  AS  
  5.  BEGIN  
  6. DECLARE @ProductId int, @Id INT, @Name VARCHAR(50), @Balance Money, @product_name VARCHAR(50)  
  7. SELECT @ProductId = product_id FROM Product ---checking valid department id  
  8. INNER JOIN  
  9. inserted on inserted.product_name = Product.product_name  
  10. select @Id=Id, @Name=Name, @Balance=Balance from inserted  
  11. Print @Id; -- Just to check value is coming or not  
  12. Print @Name;  
  13. Print @Balance;  
  14. Print @ProductId;  
  15. IF(@ProductId IS NULL)  
  16. BEGIN  
  17. PRINT 'Invalid product name. statement terminated'  
  18. END  
  19. --Finaly inserting data into customer table  
  20. INSERT INTO Customer(Id, Name, Balance, ProductId) values(@Id, @Name, @Balance, @ProductId)  
  21.  END  
We are trying to insert the record and this record will be inserted like below.
  1. INSERT INTO UV_CustomerBooking VALUES(6,'Samir',55000,'L551')  
  2. select * from Product  
  3. select * from Customer  
Output
 
 

Now again, we are inserting a record but passing the wrong vehicle model, for the same execute the script below.

  1. INSERT INTO UV_CustomerBooking VALUES(7,'Samir',55000,'L511')  
  2.  select * from Product  
  3.  select * from Customer  

Messages Output

 

Messages are showing that ‘Product Id’ got null and are showing a message as ‘Invalid product name, statement terminated’ and inserted values Id, Name, Balance in the customer table, one new row was inserted from customer table and six rows were selected from the product table and seven rows were  selected from the customer table. Wrong vehicle model does not exist in the table and it stores the value as Null.

Result Output

 

Difference between Stored Procedure and Trigger

The Triggers fire implicitly while Stored Procedures fire explicitly.

Conclusion

In this blog, I gave a brief introduction of triggers, explained the various kinds of triggers – After Triggers and Instead of Triggers -- along with their variants and explained how each of them works. I hope you will get a clear understanding of the Triggers in SQL Server and their usage.
Next Recommended Reading How to Create Log Table in Ms SQL Server with the Help of Triggers