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,

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

In SQL Server, There are three types of triggers,

DDL Trigger
DML Trigger
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.

CREATE TABLE Product
(
product_id int primary key identity,
product_name varchar(40),
unit_price money
);

Now, insert the some records in the below table.

INSERT INTO Product values('L538',7000)
INSERT INTO Product values('L550',7000)
INSERT INTO Product values('L551',7000)
SELECT * FROM product

Output

CREATE TABLE Product_histroy
(
Id int primary key identity,
Product_histroy varchar(200),
);
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

CREATE TRIGGER Trigger_Name
ON Table_Name
{INSERT [OR] | UPDATE [OR] | DELETE}
AS
Begin
--Declaration Part
--Select Part
--Executable-Code
--EXCEPTION
--Exception-Handling-Code
END;

DML Trigger can be classified into two types,

After Trigger
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,

After Insert
After Delete
After Update

After INSERT

Let’s have look at after insert trigger below.

Syntax

CREATE TRIGGER UTRG_TriggerName
ON
TableName
FOR INSERT
AS BEGIN
--Declaration Part
--Select Part
--Executable-Code
--EXCEPTION
--Exception-Handling-Code
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

CREATE TRIGGER UTRG_Product_Insert
ON
Product
FOR INSERT
AS BEGIN
DECLARE @product_id int, @product_name varchar(40), @unit_price money
SELECT @product_id = product_id, @product_name = product_name, @unit_price = unit_price FROM inserted
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)))
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.

select * from Product
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

ALTER TRIGGER UTRG_Product_Insert
ON
Product
FOR INSERT
AS BEGIN
DECLARE @product_id int, @product_name varchar(40), @unit_price money
SELECT @product_id = product_id, @product_name = product_name, @unit_price = unit_price FROM inserted
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)))
END

Here we are going to insert one record and execute the below code,

INSERT INTO Product VALUES('L575',14000)
select * from Product
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

CREATE TRIGGER UTRG_Product_Update
ON
Product
FOR UPDATE
AS BEGIN
--Declaration Part
--Select Part
--Executable-Code
--EXCEPTION
--Exception-Handling-Code
END

Example

CREATE TRIGGER UTRG_Product_Update
ON
Product
FOR UPDATE
AS BEGIN
DECLARE @product_id int
DECLARE @product_name varchar(40)
DECLARE @unit_price varchar(40)
SELECT @product_id = product_id FROM deleted
SELECT @product_name = product_name FROM deleted
SELECT @unit_price = unit_price FROM deleted
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)))
END

Example

ALTER TRIGGER UTRG_Product_Update
ON
Product
FOR UPDATE
AS BEGIN
DECLARE @product_id int
DECLARE @product_name varchar(40)
DECLARE @unit_price varchar(40)
SELECT @product_id = product_id FROM deleted
SELECT @product_name = product_name FROM deleted
SELECT @unit_price = unit_price FROM deleted
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)))
END

Execute the below snippet code to update the price and name of the product.

select * from Product
update Product
set product_name = 'L562', unit_price = 12500
where product_id = 7

Output (Product table)

Execute the below code

select * from Product
select * from Product_histroy
update Product
set product_name = 'L562', unit_price = 12500
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

CREATE TRIGGER UTRG_TriggerName
ON
TableName
FOR DELETE
AS BEGIN
--Declaration Part
--Select Part
--Executable-Code
--EXCEPTION
--Exception-Handling-Code
END

Example

CREATE TRIGGER UTRG_Product_Delete
ON
Product
FOR DELETE
AS BEGIN
DECLARE @product_id int
SELECT @product_id = product_id FROM deleted
INSERT INTO Product_histroy VALUES('An existing product with Id ' + cast (@product_id As varchar(40)) +' cost is deleted')
END

We have the below records.

select * from Product

Output

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

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

Instead Of Insert
Instead Of Update
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.

CREATE TABLE [dbo].[Customer](
[Id] [int] NOT NULL,
[Name] [varchar](50) NULL,
[Balance] [money] NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Execute the below insert script in table,

USE [ExampleDB]
GO
INSERT [dbo].[Customer] ([Id], [Name], [Balance]) VALUES (1, N'Shrimant', 25000.0000)
INSERT [dbo].[Customer] ([Id], [Name], [Balance]) VALUES (2, N'Arun', 25000.0000)
INSERT [dbo].[Customer] ([Id], [Name], [Balance]) VALUES (3, N'Kishor', 35000.0000)
INSERT [dbo].[Customer] ([Id], [Name], [Balance]) VALUES (4, N'Madhav', 45000.0000)
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.

select * from [Customer]
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.

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

Select View

SELECT * FROM UV_CustomerBooking

Output

Now we are going to insert one more record in view

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.

CREATE TRIGGER UTrig_CustomerBooking
ON UV_CustomerBooking
INSTEAD OF INSERT
AS
BEGIN
SELECT * FROM Inserted
SELECT * FROM Deleted
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.

ALTER TRIGGER UTrig_CustomerBooking
ON UV_CustomerBooking
INSTEAD OF INSERT
AS
BEGIN
DECLARE @ProductId int, @Id INT, @Name VARCHAR(50), @Balance Money, @product_name VARCHAR(50)
SELECT @ProductId = product_id FROM Product ---checking valid department id
INNER JOIN
inserted on inserted.product_name = Product.product_name
select @Id=Id, @Name=Name, @Balance=Balance from inserted
Print @Id; -- Just to check value is coming or not
Print @Name;
Print @Balance;
Print @ProductId;
IF(@ProductId IS NULL)
BEGIN
PRINT 'Invalid product name. statement terminated'
END
--Finaly inserting data into customer table
INSERT INTO Customer(Id, Name, Balance, ProductId) values(@Id, @Name, @Balance, @ProductId)
END

We are trying to insert the record and this record will be inserted like below.

INSERT INTO UV_CustomerBooking VALUES(6,'Samir',55000,'L551')
select * from Product
select * from Customer

Output

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

INSERT INTO UV_CustomerBooking VALUES(7,'Samir',55000,'L511')
select * from Product
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.

13 Comments

Join the conversation! Your thoughts help the community grow.

Pravas Ranjan8y ago
Excellent article Jitendra Bhai, please make an article of Cursor with detail explanation with code example
Sundaram Subramanian8y ago
Great one. Thanks for the article
- Jitendra Waghale8y ago
  Thanks Sundaram!!!
Saineshwar Bageri8y ago
Try to write on new features, not on common features there are lot's of article on this topic
- Jitendra Waghale8y ago
  I will try my upcoming article with new topics!!
Rajesh Kumar8y ago
Very use full article, Thanks for sharing.............
- Jitendra Waghale8y ago
  Thanks Rajesh!!
Vineet Dubey8y ago
Sir, This Article is very helpful thanku so much,
- Jitendra Waghale8y ago
  Thanks Vineet !!
Jignesh Kumar8y ago
Very good article thank you for sahring
- Jitendra Waghale8y ago
  Thanks you Jignesh!!
Mahesh Chand8y ago
Nicely done!! Thanks Jitendra.
- Jitendra Waghale8y ago
  Thank you very much sir!!