What is trigger in sql server? How to use trigger in sql server? Samples and examples

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.

Post Comment:

Notify me by email when others post comments to this article.

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:	Sign In to fill automatically.
Email:	(Will not be published, but required to validate comment)
	Type the numbers and letters shown on the left.