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.


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: