Blog post

Different types of Triggers in Sql Server

Posted on Dec 21, 2014
by Harish Kashyap
in Technology
with 0 comments

 

A trigger is a special kind of stored procedure that fires automatically when we perform a operation on a table.there are 2 types of triggers.

  1. After triggers(also called for triggers)
  2. Instead of triggers

1 .After Triggers

We can classified it into

After insert

After delete

After update

 

First Create a table tbl_triggerdemo

CREATE TABLE [dbo].[tbl_triggerdemo](

      [id] [int] IDENTITY(1,1) NOT NULL,

      [name] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

      [age] [int] NULL,

 CONSTRAINT [PK_tbl_triggerdemo] PRIMARY KEY CLUSTERED

(

      [id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

 

 

insert into tbl_triggerdemo(name,age) values('harish',24);

insert into tbl_triggerdemo(name,age) values('hemant',25);

insert into tbl_triggerdemo(name,age)values('yogesh',25);

now your table look like this

id    name        age        

1     harish      24

2     hemant      25

3     yogesh      25

 

Create another table for holding previous values.

create table trigger_operation_audit

(previousname varchar(50),previousage int,auditaction varchar(50))

 

  1. After insert

 

create trigger trg_tbl_triggerdemo_inserton on  tbl_triggerdemo

for insert

as

declare @name varchar(50);

declare @age int;

select @name=i.name from inserted i;

select @age=i.age from inserted i;

insert into trigger_operation_audit (previousname,previousage,auditaction) values(@name,@age,'inserted');

print 'after insert trigger fired'

 

 

Now insert a row into original table tbl_triggerdemo also insert it into trigger_operation_audit   

  table.

 

insert into tbl_triggerdemo(name,age) values('neeraj',25);

select * from trigger_operation_audit;   

previousname  previousage auditaction

neeraj         25          inserted

 

 

 

  1. After delete

 

create trigger trg_tbl_triggerdemo_delete on tbl_triggerdemo

for delete

as

declare @name varchar(50);

declare @age int;

select @name=d.name from deleted d;

select @age=d.age from deleted d;

insert into trigger_operation_audit (previousname,previousage,auditaction) values(@name,@age,'deleted');

print 'after delete trigger fired'

 

select * from tbl_triggerdemo;

id           name    age

1              harish    24

2              hemant                25

3              yogesh 25

4              neeraj   25

 

delete from tbl_triggerdemo where id=4;

 

select * from trigger_operation_audit;   

 

previousname    previousage  auditaction

neeraj                            25  inserted

neeraj                            25  deleted

 

 

 

  1. After update trigger

 

create trigger trg_tbl_triggerdemo_update on tbl_triggerdemo

for update

as

declare @name varchar(50);

declare @age int;

declare @auditaction varchar(50);

select @name=i.name from inserted i;

select @age=i.age from deleted i;

 

if update(name)

set @auditaction='name is updated'

 

if update (age)

set @auditaction='age is updated'

insert into trigger_operation_audit (previousname,previousage,auditaction) values(@name,@age,@auditaction);

print 'after update trigger fired'

 

select * from tbl_triggerdemo;

id      name     age

1     harish      24

2     hemant      25

3     yogesh      25

 

 

update tbl_triggerdemo set age=26 where id=3;

previousname previousage   auditaction

neeraj          25          inserted

neeraj          25          deleted

yogesh          25          age is updated

 

 

in the instead of triggers ,we have to issue another instruction within the trigger for the execution of operation.

 

Let’s create an Instead Of Delete Trigger as:

 

CREATE TRIGGER trg_InsteadOfDelete ON tbl_triggerdemo

INSTEAD OF DELETE

AS

      declare @id int;

      declare @name varchar(100);

      declare @age int;

     

      select @id=d.id from deleted d;

      select @name=d.name from deleted d;

      select @age=d.age from deleted d;

 

      BEGIN

            if(@age<26)

            begin

                  RAISERROR('Cannot delete where age < 26',16,1);

                  ROLLBACK;

            end

            else

            begin

                  delete from tbl_triggerdemo where id=@id;

                  COMMIT;

                  insert into trigger_operation_audit (previousnamename,previousage,auditaction) values(@name,@age,'Deleted -- Instead Of Delete Trigger.');

 

                  PRINT 'Record Deleted -- Instead Of Delete Trigger.'

            end

      END

GO

 

select * from tbl_triggerdemo;

 

id          name     age

1              harish    24

2              hemant                25

3              yogesh 26

 

delete from tbl_triggerdemo where id=2;

trigger is fired and following msg occur so the row will not be deleted.

Msg 50000, Level 16, State 1, Procedure trg_InsteadOfDelete, Line 15

Cannot delete where age < 26

Msg 3609, Level 16, State 1, Line 1

The transaction ended in the trigger. The batch has been aborted.

 

 

delete from tbl_triggerdemo where id=3;

 

 

the row has been deleted.

 

 

select * from tbl_triggerdemo;

id        name      age

1              harish    24

2              hemant                25

select * from trigger_operation_audit;   

 

 

 

previousname  previousage   auditaction

neeraj                            25  inserted

neeraj                            25  deleted

yogesh                          25  age is updated

yogesh                          26  deleted

yogesh                          26  Deleted -- Instead Of Delete Trigger.

 

 


0

Leave your comment

Leave your comment