Triggers in sql server with example

Triggers in Sql Server: 

Triggers are automatically fired when an action occurred like INSERT, UPDATE or DELETE (DML) is performed on a table/view in database.

Triggers are associated with the Table or View directly (Each Table/View has its own Triggers).

Types of Triggers:

There are two types of Triggers, they are

1. After(For) Triggers 

2. Instead of Triggers

1. After Triggers: 

After Triggers are executed after an action such as INSERT, UPDATE or DELETE is performed.

Types of After Triggers:

• Insert Trigger
• Update Trigger
• Delete Trigger

> Insert Trigger:

                Whenever a row is inserted in the target table, the associated trigger will be executed.

The newly inserted record is available in the 'INSERTED' table.

Ex: Syntax

        CREATE TRIGGER [TriggerName]

        ON [TableName]

        AFTER INSERT 

        AS 

        BEGIN        

        SET NOCOUNT ON;

        DECLARE @Id int

        SELECT @Id = i.Id from INSERTED i

        INSERT INTO TriggerLogTable VALUES(@Id, 'Insert')

        END

We are fetching the Id of the inserted record and the fetched value is inserted in the TriggerLogTable table. 

> Update Trigger:

                  Whenever a row is updated in the target table, the associated trigger will be executed.

The updated record is available in the 'INSERTED' table.

To find which column is updated, we need to use UPDATE() function and pass the column name of the table to it.

The UPDATE() function will return TRUE for a column if its values was updated else it will return FALSE.

Ex: Syntax

        CREATE TRIGGER [TriggerName]

        ON [TableName]

        AFTER UPDATE

        AS 

        BEGIN

        SET NOCOUNT ON;

        DECLARE @Id INT

        DECLARE @Action VARCHAR(50)

        SELECT @Id = i.Id FROM INSERTED i

 

        IF UPDATE(Name)        -- Name is Column in our Target Table

        BEGIN

              SET @Action = 'Updated Name Column'

        END

 

        IF UPDATE(Address)    -- Address is Column in our Target Table

        BEGIN

              SET @Action = 'Updated Address Column'

        END

 

       INSERT INTO TriggerLogTable VALUES(@Id, @Action)

       END

We are fetching the Id and Action of the Updated record based on the conditions then inserted in the TriggerLogTable table.

> Delete Trigger: 

                Whenever a row is deleted in the target table, the associated trigger will be executed.

The deleted record is available in the 'DELETED' table.

Ex: Syntax

        CREATE TRIGGER [TriggerName]

        ON [TableName]

        AFTER DELETE

        AS 

        BEGIN        

        SET NOCOUNT ON;

        DECLARE @Id int

        SELECT @Id = d.Id from DELETED d

        INSERT INTO TriggerLogTable VALUES(@Id, 'Deleted')

        END

We are fetching the Id of the deleted record and the fetched value is inserted in the TriggerLogTable table.

2. Instead of Triggers

Instead of Triggers are executed instead of any of the INSERT, UPDATE or DELETE operations.

Which means to skip the INSERT/UPDATE/DELETE statement on a table.

Types of INSTEAD OF Triggers:

• INSTEAD OF INSERT Trigger
• INSTEAD OF UPDATE Trigger
• INSTEAD OF DELETE Trigger

For Example an INSTEAD OF DELETE Trigger, 

    Whenever anyone tries to delete a row from the target table the associated trigger will be executed.

Ex: Syntax

        CREATE TRIGGER [TriggerName]

        ON [TableName]

        INSTEAD OF DELETE

        AS 

        BEGIN        

        SET NOCOUNT ON;

        DECLARE @Id int

        SELECT @Id = d.Id from DELETED d

IF @Id = 5  -- Any condition we can check here based on our requirement

BEGIN

RAISERROR('Rajkumar''s record cannot be deleted',16 ,1)

        ROLLBACK

        INSERT INTO TriggerLogTable VALUES(@Id, 'Record cannot be deleted.')

        END

        ELSE

        BEGIN

        DELETE FROM TableName WHERE Id = @Id

  INSERT INTO TriggerLogTable VALUES(@Id, 'Instead Of Delete done')

        END

END

Any condition we can check before delete operation, if sensitive data or admin records which can't delete by accidentally or some times the user didn't have privileges to delete records from that table then it's suitable to fire this trigger and log the information who tried to did this delete operation at any time.

INSTEAD OF DELETE triggers cannot be defined on a table that has a foreign key defined with a DELETE action.

Same approach for the INSTEAD OF INSERT Triggers and INSTEAD OF UPDATE Triggers.

Thanks

Rajkumar