Trigger

Trigger:

èAfter Triggers (For Triggers)

èInstead Of Triggers

 After Triggers

These triggers run after an insert, update or delete on a table. They are not supported for views. 
AFTER TRIGGERS can be classified further into three types as:

     AFTER INSERT Trigger.

     AFTER UPDATE Trigger.

    AFTER DELETE Trigger.

CREATE TABLE Employee_Test

(

Emp_ID INT Identity,

Emp_name Varchar(100),

Emp_Sal Decimal (10,2)

)

INSERT INTO Employee_Test VALUES ('Anees',1000);

INSERT INTO Employee_Test VALUES ('Rick',1200);

INSERT INTO Employee_Test VALUES ('John',1100);

INSERT INTO Employee_Test VALUES ('Stephen',1300);

INSERT INTO Employee_Test VALUES ('Maria',1400);

AFTER INSERT TRIGGER

CREATE TABLE Employee_Test_Audit

(

Emp_ID int,

Emp_name varchar(100),

Emp_Sal decimal (10,2),

Audit_Action varchar(100),

Audit_Timestamp datetime

)

 After Insert Trigger

This trigger is fired after an INSERT on the table.

CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test]

FOR INSERT

AS

          declare @empid int;

          declare @empname varchar(100);

          declare @empsal decimal(10,2);

          declare @audit_action varchar(100);

          select @empid=i.Emp_ID from inserted i; 

          select @empname=i.Emp_Name from inserted i;

          select @empsal=i.Emp_Sal from inserted i;        

          set @audit_action='Inserted Record -- After Insert Trigger.';

          insert into Employee_Test_Audit

           (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

          values(@empid,@empname,@empsal,@audit_action,getdate());

          PRINT 'AFTER INSERT trigger fired.'

GO

To see the newly created trigger in action, lets insert a row into the main table as:

insert into Employee_Test values('Chris',1500);

Now, a record has been inserted into the Employee_Test table. The AFTER INSERT trigger attached to this table has inserted the record into the Employee_Test_Audit as:

AFTER UPDATE Trigger

This trigger is fired after an update on the table. Let’s create the trigger as:

CREATE TRIGGER trgAfterUpdate ON [dbo].[Employee_Test]

FOR UPDATE

AS

          declare @empid int;

          declare @empname varchar(100);

          declare @empsal decimal(10,2);

          declare @audit_action varchar(100);

          select @empid=i.Emp_ID from inserted i; 

          select @empname=i.Emp_Name from inserted i;

          select @empsal=i.Emp_Sal from inserted i;        

         

          if update(Emp_Name)

                   set @audit_action='Updated Record -- After Update Trigger.';

          if update(Emp_Sal)

                   set @audit_action='Updated Record -- After Update Trigger.';

          insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

          values(@empid,@empname,@empsal,@audit_action,getdate());

          PRINT 'AFTER UPDATE Trigger fired.'

GO

AFTER UPDATE

update Employee_Test set Emp_Sal=1550 where Emp_ID=6

This inserts the row into the audit table as:

After Update Trigger.   

(c) AFTER DELETE Trigger

This trigger is fired after a delete on the table

CREATE TRIGGER trgAfterDelete ON [dbo].[Employee_Test]

AFTER DELETE

AS

          declare @empid int;

          declare @empname varchar(100);

          declare @empsal decimal(10,2);

          declare @audit_action varchar(100);

          select @empid=d.Emp_ID from deleted d;

          select @empname=d.Emp_Name from deleted d;         

          select @empsal=d.Emp_Sal from deleted d;       

          set @audit_action='Deleted -- After Delete Trigger.';

          insert into Employee_Test_Audit

(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

          values(@empid,@empname,@empsal,@audit_action,getdate());

          PRINT 'AFTER DELETE TRIGGER fired.'

GO

-- After Delete Trigger.  All the triggers can be enabled/disabled on the table using the statement

ALTER TABLE Employee_Test {ENABLE|DISBALE} TRIGGER ALL

Specific Triggers can be enabled or disabled as:

ALTER TABLE Employee_Test DISABLE TRIGGER trgAfterDelete

This disables the After Delete Trigger named trgAfterDelete on the specified table.

è Instead Of Triggers

INSTEAD OF TRIGGERS can be classified further into three types as:

=>      INSTEAD OF INSERT Trigger.

=>       INSTEAD OF UPDATE Trigger.

=>       INSTEAD OF DELETE Trigger.

Let’s create an Instead Of Delete Trigger as:

CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Employee_Test]

INSTEAD OF DELETE

AS

          declare @emp_id int;

          declare @emp_name varchar(100);

          declare @emp_sal int;

         

          select @emp_id=d.Emp_ID from deleted d;

          select @emp_name=d.Emp_Name from deleted d;

          select @emp_sal=d.Emp_Sal from deleted d;

          BEGIN

                   if(@emp_sal>1200)

                   begin

                             RAISERROR('Cannot delete where salary > 1200',16,1);

                             ROLLBACK;

                   end

                   else

                   begin

                             delete from Employee_Test where Emp_ID=@emp_id;

                             COMMIT;

                             insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

                             values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of Delete Trigger.',getdate());

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

                   end

          END

GO