Triggers

Triggers are sql queries, which will be sat on a table and executes (fires) immediately after DML commands execution is called “After Triggers”. The triggers that are execute before the DML commands execution on a table called “Instead Of Triggers”.

After Triggers

The basic aim of “triggers” is to audit (Record) the data of a table, when it has inserted or updated. (When, What, Who)

For example I want to know the information like …. Who has inserted the data, when it has inserted and which row has inserted. For this I can create an INSERT trigger. As same I can create triggers for UPDATE and DELETE operations.

An INSERT trigger fires immediately after when an INSERT operation happened on the table,

An UPDATE trigger fires immediately after when an UPDATE operation happened on the table,

A DELETE trigger fires immediately after when a DELETE operation happened on the table,

Let’s assume that we have a table Employee, and create another table Tr_Employee. Here the main table Employee audit information will be maintained in Tr_Employee table.

      CREATE TABLE [dbo].[Tr_Employee](

            [EMPNO] [int],

            [Action] [varchar](10),

            [Updated On] [datetime],

            [Updated By] [varchar](20) )

Insert Triggers:  Will create on Employee table and fired after an INSERT statement on the table;

      CREATE TRIGGER trg_INSEmployee

      ON [dbo].[Employee]

      FOR INSERT

      AS

            declare @Empno int;

            declare @Action varchar(100);       -- What

            declare @UpdatedOn datetime;        -- When

            declare @UpdatedBy varchar(100);    -- Who

            select @Empno = inserted.EMPNO from inserted;  

            set @Action='INSERTED';

            select @UpdatedBy = SYSTEM_USER

           

            insert into Tr_Employee       -- Inserts respective information to Tr_Employee table

            values(@Empno,@Action,getdate(),@UpdatedBy);

      GO

Insert Trigger is created on Employee table, now insert a row to Employee, automatically insert trigger fired after the INSERT command and records the respective information to Tr_Employee table.

INSERT INTO Employee VALUES (7952,'REDDY','MANAGER',7666,'1982-01-23',6500.00,NULL,10)

Check the main table,

SELECT * FROM Employee

Check the Audit table,

SELECT * FROM Tr_Employee

Update Triggers:  Will create on Employee table and fired after an UPDATE statements on the table;

CREATE TRIGGER trg_UPDEmployee

      ON [dbo].[Employee]

      FOR UPDATE

      AS

            declare @Empno int;

            declare @Action varchar(100);       -- What

            declare @UpdatedOn datetime;        -- When

            declare @UpdatedBy varchar(100);    -- Who

            select @Empno = inserted.EMPNO from inserted;  

           

if update(ENAME) OR update(JOB)OR update(HIREDATE)OR update(SAL)OR update(DEPTNO)

            set @Action='UPDATED';

            select @UpdatedBy = SYSTEM_USER

           

            insert into Tr_Employee       -- Inserts respective information to Tr_Employee table

            values(@Empno,@Action,getdate(),@UpdatedBy);

      GO

Update Trigger is created on Employee table, now update a value to Employee, automatically update trigger fired after the UPDATE command and records the respective information to Tr_Employee table.

UPDATE Employee

SET SAL= SAL+600

WHERE EMPNO = 7369

Check the main table,

SELECT * FROM Employee

Check the Audit table,

SELECT * FROM Tr_Employee

Delete Triggers:  Will create on Employee table and fired after the DELETE statements on the table;

      CREATE TRIGGER trg_DELEmployee

      ON [dbo].[Employee]

      FOR DELETE

      AS

            declare @Empno int,@ENAME varchar(10),@JOB varchar(10),

  @HIREDATE date, @SAL money, @DEPTNO int

            declare @Action varchar(100);       -- What

            declare @UpdatedOn datetime;        -- When

            declare @UpdatedBy varchar(100);    -- Who

            select @Empno = deleted.EMPNO from deleted;    

            select @ENAME = deleted.ENAME from deleted;

            select @JOB = deleted.JOB from deleted;

            select @HIREDATE = deleted.HIREDATE from deleted;

            select @SAL = deleted.SAL from deleted;

            select @DEPTNO = deleted.DEPTNO from deleted;

           

            SET @Action='DELETED';

            SELECT @UpdatedBy = SYSTEM_USER

           

            insert into Tr_Employee       -- Inserts respective information to Tr_Employee table

            values(@Empno,@Action,getdate(),@UpdatedBy);

      GO

Delete Trigger is created on Employee table, now Delete a value to Employee, automatically Delete trigger fired after the DELETE command and records the respective information to Tr_Employee table.

DELETE FROM Employee

WHERE EMPNO = 7369

Check the main table,

SELECT * FROM Employee

Check the Audit table,

SELECT * FROM Tr_Employee