What Are Triggers In SQL Server

Triggers

Triggers, automatically executed or fired when some changes occurs in database like UPDATE, DELETE and INSERT. Triggers are used for security of the data, so that data remain unchanged until we change it. We can say they are the special kind of stored procedure.

General Method of Writing Triggers

CREATE TRIGGER TRIGGERNAME

     ON TABLENAME

     AFTER EVENT

     AS BEGIN

     'YOUR LOGIC GOES HERE'

        END

The CREATE TRIGGER statement is used to create the trigger and ON specifies the table on which trigger will be attached. AFTER EVENT specifies that this is After Trigger.

Types of Triggers

There two types of triggers

1. After Triggers
2. Instead Of Triggers

1. After Triggers

These triggers run after an insert, update or delete. Let’s try to understand with the help of an example.

Create a table employee and insert some dummy records in it.

--CREATING TABLE EMPLOYEES

CREATE TABLE EMPLOYEES

(

ID INT IDENTITY PRIMARY KEY,

NAME VARCHAR(20),

);

INSERT INTO EMPLOYEES VALUES ('SYMOND')

INSERT INTO EMPLOYEES VALUES ('MICHAEL')

INSERT INTO EMPLOYEES VALUES ('GREGG')

INSERT INTO EMPLOYEES VALUES ('JOHN')

At this point I am going to create another table “Audit_Employees” in which we are going to record the changes that will happen in the main table.

--CREATING TABLE AUDIT EMPLOYEES

CREATE TABLE AUDIT_EMPLOYEES

(

ID INT,

NAME VARCHAR(20),

[ACTION] VARCHAR(100),

[DATE] DATE

)

Now it’s time to create an after Insert Triggers so here we go.

After Insert Trigger

--CREATING INSERT TRIGGERS

CREATE TRIGGER AFTERINSERT

       ON EMPLOYEES

              AFTER INSERT

AS BEGIN

              DECLARE @ID INT

              DECLARE @NAME VARCHAR(20)

              DECLARE @A VARCHAR(30)

              SELECT @ID=I.ID FROM INSERTED I /* INSERTED IS AUTOMATICALLY CREATED TABLE.ONLY USED IN TRIGGERS.CAN’T USED OUTSIDE THE TRIGGERS */

              SELECT @NAME=I.NAME FROM INSERTED I

              SET @A='INSERT TRIGGER IS FIRED'

              INSERT INTO AUDIT_EMPLOYEES VALUES (@ID, @NAME, @A, GETDATE())

      

       PRINT 'INSERT TRIGGER IS FIRED'

END

INSERTED is a logical table that is automatically created and is only used in triggers and not outside of them.

2. Instead Of Triggers

These triggers are used as an interceptor for anything that anyone tried to do on your table or view. For example if you define and Instead Of Trigger on your Employee table and anyone tries to delete record from employee table the record will not get deleted. Let’s understand with the help of an example.

CREATE TRIGGER INSTEAD_OF_DELETE ON [EMPLOYEES]

INSTEAD OF DELETE

AS

BEGIN

       DECLARE @ID INT;

       DECLARE @NAME VARCHAR(100);

      

      

       SELECT @ID=D.EMP_ID FROM DELETED D;

       SELECT @NAME=D.EMP_NAME FROM DELETED D; 

       BEGIN

              IF(@NAME = 'JOHN')

              BEGIN

                     RAISERROR('CANNOT DELETE WHERE NAME  = JOHN',16,1);

                     ROLLBACK;

              END

              ELSE

              BEGIN

                     DELETE FROM [EMPLOYEES] WHERE EMP_ID=@EMP_ID;

                     COMMIT;

                     INSERT INTO AUDIT_EMPLOYEES(ID,NAME,[ACTION],[DATE])

                     VALUES(@ID,@NAME,'DELETED -- INSTEAD OF DELETE TRIGGER.',GETDATE());

                     PRINT 'RECORD DELETED -- INSTEAD OF DELETE TRIGGER.'

              END

       END

END

This trigger will prevent the deletion of records from the table where Name = 'John'. If user will try to delete such a record where name is john the Instead Of Trigger will rollback the transaction.