A database trigger is procedural code that is automatically executed in response to certain events on a  particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database.A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically.You can’t explicitly invoke triggers. The only way to do this is by performing the required action to the table that they are assigned to.

Types of Triggers

There are mainly two types of triggers associated with each of action performed ie. Insert, Update and Delete. They are:

1.    After/For Triggers

2.   Instead Of Triggers

AFTER/FOR Triggers

These triggers fire after/for an insert, update or delete on table associated. They are not supported for views.

AFTER Triggers can be classified further into three types:
  1.    AFTER INSERT Trigger.
  2.    AFTER UPDATE Trigger.
  3.    AFTER DELETE Trigger.

Before creating AFTER Trigger I would like to create two tables one which will keep record of employee (tblEmployee) and another keeping record of action performed on that table (tblEmployeeTable_Action)

create table tblEmployeeTable_Action
(nID int Identity,
sName varchar(50),
sAddress varchar(100))
 
CREATE TABLE tblEmployeeTable_Action
(nID int,
sName varchar(50),
sAddress varchar(100),
sAction varchar(20),
dDateOfAction datetime)

 

Now I am going to dump some fake records in table tblEmployee.

insert into tblEmployee values ('Mac', 'California')
insert into tblEmployee values ('Raj', 'Banglore')
insert into tblEmployee values ('John', 'California')
insert into tblEmployee values ('Mark', 'London')
insert into tblEmployee values ('Steve', 'California')
AFTER/FOR INSERT Trigger

This trigger is fired after an INSERT on the table. Basic syntax of creating AFTER INSERT Trigger is

 

CREATE TRIGGER TriggerName

ON TableName

AFTER/FOR INSERT

AS

    TriggerCode

 

Example

CREATE TRIGGER trgAfterInsert  ON tblEmployee
AFTER INSERT
AS
      declare @ID int;
      declare @Name varchar(100)   
      declare @Address varchar(10)
     
      select @ID=nID from inserted 
      select @Name=sName from inserted   
      select @Address=sAddress from inserted
     
      insert into tblEmployeeTable_Action values(@ID,@Name,@Address,'After Insert',getdate())
 
      PRINT 'AFTER INSERT trigger fired.'
GO

 

The CREATE TRIGGER statement is used to create the trigger. THE ON clause specifies the table name on which the trigger is to be attached. The FOR INSERT specifies that this is an AFTER INSERT trigger. In place of FOR INSERT, AFTER INSERT can be used. Both of them mean the same. 


In the trigger body, table named inserted has been used. This table is a logical table and contains the row that has been inserted. I have selected the fields from the logical inserted table from the row that has been inserted into different variables, and finally inserted those values into the Audit table.

AFTER/FOR UPDATE Trigger

This trigger is fired after an update on the table. The basic syntax is:

 

CREATE TRIGGER TriggerName

ON TableName

AFTER/FOR UPDATE

AS

    TriggerCode

 

Example
CREATE TRIGGER trgAfterInsert  ON tblEmployee
AFTER UPDATE
AS
      declare @ID int;
      declare @Name varchar(100)   
      declare @Address varchar(10)
     
      select @ID=nID from inserted 
      select @Name=sName from inserted   
      select @Address=sAddress from inserted
     
      insert into tblEmployeeTable_Action values(@ID,@Name,@Address,'After Update',getdate())
 
      PRINT 'AFTER UPDATE trigger fired.'
GO
AFTER/FOR Delete Trigger

This trigger is fired after a delete on the table.

Syntax
CREATE TRIGGER TriggerName
ON TableName
AFTER/FOR DELETE
AS
    TriggerCode
 
CREATE TRIGGER trgAfterInsert ON tblEmployee
AFTER DELETE
AS
      declare @ID int;
      declare @Name varchar(100)   
      declare @Address varchar(10)
     
      select @ID=nID from inserted 
      select @Name=sName from inserted   
      select @Address=sAddress from inserted
     
      insert into tblEmployeeTable_Action values(@ID,@Name,@Address,'After Delete',getdate())
 
      PRINT 'AFTER DELETE trigger fired.'
GO
INSTEAD OF Triggers

While an AFTER trigger can be applied to a table only, an "instead of" trigger can be associated with either a table or a view. If you use the INSTEAD OF expression, the trigger starts when the table or view is opened but before a change has taken place. The difference with the AFTER trigger is that, this time, you can perform some action(s) before the change is made on the table or view. This also implies that, if the code of the trigger is to create a new record, at this time, the record does not yet exist, which means you cannot catch that record. At this time also, you can prevent the record from being created

Syntax

CREATE TRIGGER TriggerName

ON TableOrViewName

INSTEAD OF INSERT/UPDATE/DELETE

AS

    TriggerCode

 
Example
CREATE TRIGGER InsteadOfInsert
ON tblEmployee
INSTEAD OF INSERT
AS
      print 'Attempt to insert record'
GO

 

Above Trigger will be fired when someone tries to new record in tblEmployee and prevent insertion of record into the table.
You can also read these related post

https://www.mindstick.com/Articles/569/trigger-in-sql-server

https://www.mindstick.com/Articles/1299/trigger-in-sql-server



Leave Comment