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)

createtable tblEmployeeTable_Action
(nID intIdentity,
sName varchar(50),
 sAddress varchar(100))
  
CREATETABLE 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.

insertinto tblEmployee values ('Mac','California')
insertinto tblEmployee values ('Raj','Banglore')
insertinto tblEmployee values ('John','California')
insertinto tblEmployee values ('Mark','London')
 insertinto 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

CREATETRIGGER TriggerName

ON TableName

AFTER/FORINSERT

AS

    TriggerCode

Example

CREATETRIGGER trgAfterInsert  ON tblEmployee
AFTERINSERT
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
      
      insertinto 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:

CREATETRIGGER TriggerName

ON TableName

AFTER/FORUPDATE

AS

    TriggerCode

Example

CREATETRIGGER trgAfterInsert  ON tblEmployee
AFTERUPDATE
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
      
      insertinto 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

CREATETRIGGER TriggerName
ON TableName
AFTER/FORDELETE
AS
    TriggerCode
 
CREATETRIGGER trgAfterInsert  ON tblEmployee
AFTERDELETE
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
      
      insertinto 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

CREATETRIGGER TriggerName

ON TableOrViewName

INSTEADOFINSERT/UPDATE/DELETE

AS

    TriggerCode

 
Example

CREATETRIGGER InsteadOfInsert
ON tblEmployee
INSTEADOFINSERT
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