Types of Triggers and Creating Triggers

In this blog let us see how many types of trigger we can find and how to create them. Lets not waste any time then.

There are two kinds of triggers.
-After Triggers
-Instead of Triggers

After Triggers are of 3 types
-After Insert
-After Update
-After Delete

Instead of Triggers are of 3 types again.
-Instead of Insert
-Instead of Update
-Instead of Delete

Lets see an example in each category by creating the triggers and executing them

First, create a simple table with two columns.

CREATE TABLE [dbo].[TestTable](
    [EmpId] [varchar](50) NULL,
    [Name] [varchar](255) NULL
)


Create another table where the data will be inserted by the trigger.

CREATE TABLE [dbo].[AFTERINSERT](
    [EmpId] [varchar](50) NULL,
    [Name] [varchar](255) NULL
)


Insert some data into the TestTable.

INSERT INTO TESTTABLE
VALUES('01','ABC')

INSERT INTO TESTTABLE
VALUES('02','BCD')

Now create the below trigger that will insert the data into AfterInsert table and also print the data that has been inserted into TestTable

InsertTrigger is the name of the trigger and since we are creating trigger on TestTable for and insert we mention that in the trigger which is clearly readable.

In the body of the trigger, we create two variables to hold the data from INSERTED table. This is not a physical table but a logical one that holds the just inserted data.

And that data is inserted into the AfterInsert table.
 
CREATE TRIGGER INSERTTRIGGER ON TESTTABLE
FOR INSERT

AS

DECLARE @EMPID VARCHAR(255)
DECLARE @NAME VARCHAR(255)

SET @EMPID = I.EMPID FROM INSERTED I
SET @NAME = I.NAME FROM INSERTED

--PRINT THE ID AND THE NAME
PRINT @EMPID + ' ' + @NAME

--INSERT INTO AFTERINSERT TABLE
INSERT INTO AFTERINSERT
VALUES(@EMPID,@NAME)

GO

Now try inserting data into the TestTable by using this line

Insert into TestTable values('03', 'CDE')

You can create similar triggers for Update and Delete as well. For Update, there is no logical table, but have to use Inserted. For Delete, there is a table called Deleted. Use Deleted instead of Inserted in the above trigger for deletion.

Now lets create a trigger for Instead Of Delete. This can be used to intercept any modifications to the table like insert, update or delete.

Below example is for Instead of delete.

As explained before, since we are checking on TestTable, we have to mention the table name and Instead Of delete.

Most of the body is same except that, we are checking if emp id is '01'. If yes, then prints the message and rollsback. if not deletes it.

Create the below trigger and try to delete the emp id '01'.

CREATE TRIGGER INSTDDELETE ON TESTTABLE
INSTEAD OF DELETE

AS

DECLARE @EMPID VARCHAR(255)
DECLARE @NAME VARCHAR(255)

SET @EMPID = I.EMPID FROM DELETED I
SET @NAME = I.NAME FROM DELETED I

IF @EMPID = '01'
    BEGIN
        --PRINT THE ID AND THE NAME
        PRINT @EMPID + ' ' + @NAME + ' CANNOT BE DELETED'
        ROLLBACK
    END

GO

Similarly we can create Instead of triggers for Update and Insert as well. Instead of using Deleted table use Inserted table.