Triggers in sql server

Triggers in sql server

In simple Language Trigger is Database object which is execute on table in response of insert, update and delete action performed on database table.

Types of triggers

As Insert, Update and delete action performed on table by triggers it is also 3 type.

But we mainly classified it in two parts.

a)After triggers

b)Instead of trigger.

Before working on trigger we need to create table .Here I am creating two tables  one for master Data insertion (KANDY) and another one for its tack record(kandt_Test_trigger).

CREATE TABLE kandy

(

ID INT Identity,

name Varchar(100),

Amount Decimal (10,2)

)

Populate some data.

INSERT INTO kandy VALUES ('kandy',9000);

INSERT INTO kandy VALUES ('sam',1290);

INSERT INTO kandy VALUES ('sandy',1900);

INSERT INTO kandy VALUES ('rahul',9300);

INSERT INTO kandy VALUES ('sahil',780);

CREATE TABLE kandt_Test_trigger

(

ID int,

name varchar(100),

Amount decimal (10,2),

Action varchar(100),

time datetime

)

(A)After Trigger

This trigger perform action on table.it is not for Views.

It also further divide in to three sub parts….

1)After Insert trigger

2)After Update trigger

3)After Delete Trigger

After Insert Trigger

This trigger fire on table whenever a new record going to insert.

Syntax :

create TRIGGER tbl_Insert ON [dbo].[kandy]

FOR INSERT

AS

      declare @id int;

      declare @name varchar(100);

      declare @amount decimal(10,2);

      declare @action varchar(100);

      select @id=i.ID from inserted  i;  

      select @name=i.Name from inserted  i;    

      select @amount=i.amount from inserted  i;

      set @action='success.';

      insert into kandt_Test_trigger

           (ID,Name,amount,Action,time)

      values(@id,@name,@amount,@action,getdate());

      PRINT 'Success'

GO

Insert new record:

 insert into kandy values('roni',1500);

 select * from Employee_Test_Audit

 select * from Employee_Test

After Update Trigger :

This type of trigger fire on table when any type updation is happen on table.

Syntax :

CREATE TRIGGER tbl_Update ON [dbo].[kandy]

FOR UPDATE

AS

      declare @id int;

      declare @name varchar(100);

      declare @amount decimal(10,2);

      declare @action varchar(100);

      select @id=i.ID from inserted i;   

      select @name=i.Name from inserted i;     

      select @amount=i.amount from inserted i; 

     

      if update(Name)

            set @action='Update name';

      if update(amount)

            set @action='Updateamount';

      insert into kandt_Test_trigger(ID,Name,amount,Action,time)

      values(@id,@name,@amount,@action,getdate());

      PRINT 'sucess.'

GO

Update statement :

 update kandy set Amount=1256 where ID=8

 select * from kandy

 select * from kandt_Test_trigger

After Update Trigger :

After Update Trigger is fire when record is deleted from table.

Syntax :

CREATE TRIGGER TBL_Delete ON [dbo].[kandy]

AFTER DELETE

AS

      declare @id int;

      declare @name varchar(100);

      declare @amount decimal(10,2);

      declare @action varchar(100);

      select @id=d.ID from deleted d;    

      select @name=d.Name from deleted d;

      select @amount=d.amount from deleted d;  

      set @action='Deleted';

      insert into kandt_Test_trigger

(ID,Name,amount,Action,time)

      values(@id,@name,@amount,@action,getdate());

      PRINT 'success.'

GO

Delete statement :

delete from kandy where ID=8

select * from kandy

select * from kandt_Test_trigger