What is trigger in sql server? How to use trigger in sql server?
In this article I am going to explain in detail about Trigger concept in SQL SERVER. How the trigger event is performed in sql server with detailed explanation and example.
Description
Trigger is used perform operation in SQL server after insert or update or delete values in to the table. Trigger is automatically fire and execute code when ever record is insert or update or delete without manual interruption. It is used to increase performance in Data processing.Example1
In this example I have used one table like below structure
create table emp (ID int primary key identity(1,1), ename varchar(30), BasicPay int, HRA int, Total int)
in that table I have update total amount field after insert or update record in that table mean every time record is inserted / updated in that table automatically total field values are updated.
I have write TRIGGER for Basic pay, HRA amount like below
CREATE TRIGGER [dbo].[TRGEMP]
ON [dbo].[emp]
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @i INT
Select @i = ID FROM Inserted
update emp set Total=BasicPay+HRA where ID = @i
END
That's all now coding part is over. Now run inserted statement to test how trigger is working
insert into emp(ename,BasicPay,HRA) values('ravi','45000','500')Example2
In this example I have used two tables whatever I insert into first table then the second table automatically filled that row using trigger.
Create table like below
create table leave(id int,Name varchar(50),days varchar(40))
create table leave_permit(id int,Name varchar(50),days varchar(40))
Write trigger like below code
CREATE TRIGGER [dbo].[TRGLEAVE]
ON [dbo].[leave]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @id INT
DECLARE @Name VARCHAR(50)
DECLARE @days VARCHAR(50)
Select @id = id,@Name=Name,@days=days FROM Inserted
insert into leave_permit values(@id,@Name,@days)
END
Now insert values in the leave table then check automatically same row inserted in the leave_permit table.
insert into leave values('1','Ravi','5')Conclusion
I hope this article is help you to know about TRIGGER concept in SQL SERVER.