SQL Triggers

What is a SQL Trigger?
Trigger is a special stored procedure which is executed upon firing of certain event. If you are aware of stored procedures, these are the block of compiled SQL statements being executed for some specific purpose.

So what is the difference between stored procedures and triggers?
Though both of them are coded in same way but while executing the same, there it makes a difference. Stored procedures can be called by your application code or by any other stored procedures, where as the triggers are only get called upon some specific database events as insert, update or delete.

Purpose of creating a Trigger
Triggers are generally created to maintain an audit table. The need of the audit table is, you want to keep track of different operations being carried out over a table. For this we need to create a separate table and can name it logically meaning audit table for some specific table. Ex. emp_audit

Types of Triggers
Triggers are basically of two types.
a. For/After Triggers
b. Instead Of Triggers

a. What is For/After Triggers
These are the triggers those execute after certain operation as insert/update/delete operation is done. Hence as per the operations the triggers are associated, they have been classified as that.

1. After Insert Trigger
2. After Update Trigger
3. After Delete Trigger

* Though we have grouped the triggers as above, but we can have a single trigger that will be executed after insert, update and delete operation.

Before I would explain about the trigger and their examples, let me first create a platform for that, that is let us create one dummy table and its audit table to be used in the example.

Suppose the table that we want to apply trigger, is "Employee". And the audit table for the same is 'Emp_audit'.

Employee table create statement :

CREATE TABLE Employee(

Id INT Identity,
EmpName VARCHAR(100),
EmpSalary DECIMAL (10,2)
)

Emp_audit table create statement:

CREATE TABLE Emp_audit(

Id INT,
EmpName VARCHAR(100),
EmpSalary DECIMAL (10,2),
ActionTaken VARCHAR(100),
AuditTimestamp DATETIME
) 

In the above mentioned audit table, we will keep the employee table record details along with the operation being made and the time stamp for the same.

1. After Insert Trigger
When we define a trigger as this, these are executed after the insert operation is done over the specified table(The table on which the trigger is written). So to keep track of the insertion being made, we can use this type of trigger and stored the inserted record details in some audit table.

Example:

CREATE TRIGGER afterInsertTrigger ON Employee 
FOR INSERT
AS
    DECLARE @id INT;
    DECLARE @empName VARCHAR(100);
    DECLARE @empSal DECIMAL(10,2);
    DECLARE @actionPerformed VARCHAR(100) = 'Employee record inserted.';
    DECLARE @currentDateTime DATETIME = getDate();

    -- Here inserted is the logical table which holds the last record being inserted
    SELECT @id = Id
        , @empname = EmpName
        , @empsal = EmpSalary
    FROM inserted;   

    -- Inserting the obtained data into emp_audit table
    INSERT INTO Emp_audit
    (
        Id
        , EmpName 
        , EmpSalary 
        , ActionTaken 
        , AuditTimestamp 
    ) 
    VALUES
    (
        @id
        , @empName
        , @empSal
        , @actionPerformed
        , @currentDateTime
    );

    PRINT 'One record inserted into employee table.'

 Explanation:

Here we have got the inserted record details from 'inserted'(a logical table) table that keeps track of the last inserted record details. Then we used the retrieved data to insert into the emp_audit table.

2. After Update Trigger
These type of triggers are used to keep track of the update operation on tables. Please find the example below for having detailed idea on the same.

Example:

CREATE TRIGGER afterUpdateTrigger ON Employee 

FOR UPDATE

AS

    DECLARE @id INT;
    DECLARE @empName VARCHAR(100);
    DECLARE @empSal DECIMAL(10,2);
    DECLARE @actionPerformed VARCHAR(100);
    DECLARE @currentDateTime DATETIME = getDate();

     -- Here inserted is the logical table which holds the last record being inserted
    SELECT @id = Id
        , @empname = EmpName
        , @empsal = EmpSalary
    FROM inserted;   

    if update(EmpName)
        set @actionPerformed ='Name modified.';
    if update(EmpSalary)
        set @actionPerformed ='Salary modified.';

    -- Inserting the obtained data into emp_audit table
    INSERT INTO Emp_audit

    (
        Id
        , EmpName 
        , EmpSalary 
        , ActionTaken 
        , AuditTimestamp 
    ) 

    VALUES
    (
        @id
        , @empName
        , @empSal
        , @actionPerformed
        , @currentDateTime
    );
    PRINT 'One record updated in employee table.'

Explanation:

Here if you compare the after update trigger with after insert trigger, there is not a much difference
between both. Both use inserted logical table to get the inserted or updated record details. The only difference is you can detect the column being updated using 'update(column_name)' method. This method speaks about whether a column has been modified or not.

 3. After Delete Trigger

This trigger helps in keep track of the records being deleted. Here the example would be just the same as above insert and update trigger, but here we will get details about the deleted records from deleted logical table instead of inserted.

b. Instead Of Triggers
This type of trigger is used as a checkpoint for database operations. i.e. while you would go for database operation, before it actually committing the changes, it goes for execution of this trigger and here in this trigger we can have conditional check to see whether to commit the changes or rollback the same. Please see below example for a clear idea on this.

Example:

CREATE TRIGGER insteadOfDeleteTrigger ON Employee 
INSTEAD OF DELETE
AS
    DECLARE @id INT;
    DECLARE @empName VARCHAR(100);
    DECLARE @empSal DECIMAL(10,2);
    DECLARE @actionPerformed VARCHAR(100) = 'One employee record deleted';
    DECLARE @currentDateTime DATETIME = getDate();
    
    -- Getting the deleted record details from 'deleted' logical table
    SELECT @id = Id
        , @empname = EmpName
        , @empsal = EmpSalary
    FROM deleted;

    BEGIN
    
        -- Here under some condition either we are making rollback of the changes or we are committing the same
        if(@empsal < 1000)
            begin
                RAISERROR('Employees with salary less than 1000 can not be deleted',16,1);
                ROLLBACK;
            end
        else
            begin
                DELETE FROM Employee 
                    WHERE Id = @id;
                COMMIT;
                INSERT INTO Emp_audit
                (
                    Id
                    , EmpName 
                    , EmpSalary 
                    , ActionTaken 
                    , AuditTimestamp 
                ) 
                VALUES
                (
                    @id
                    , @empName
                    , @empSal
                    , @actionPerformed
                    , @currentDateTime
                );

                PRINT 'One record is deleted from employee table.'
            end
    END

Explanation:

As you can see in above example, during deleting any record, it first checks if the record satisfies certain condition. If only it satisfies the condition, then only we will commit the changes else we will rollback it.