Triggers in SQL Server

Posted by  Haider M Rizvi
on    January-01-2011 20:45 PM

Database Database 
Ratings:
1 Comment(s)
 7704  View(s)
Rate this:

Triggers in SQL Server

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.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:

         i.            AFTER INSERT Trigger.

       ii.            AFTER UPDATE Trigger.

      iii.            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.


Trigger in SQL.

By Rohit Kesharwani on   2 years ago
nice article. provide good information.















Recent Activities

jayprakash sharma Created New Discussion Extracting texts from html tags   14 hours ago

Anchal Kesharwani added new Blog How to Use JSON   15 hours ago

Sumit Kesarwani added new Article Custom Error Page in Asp.Net Mvc 4   16 hours ago


Top Contributors

Don't want to miss updates? Please click the below button!