Article
    C#
    ADO.Net
    .NET
    ASP.Net & Web Forms
    Custom Controls
    Web Development
    Exception Handling
    XML
    Database
    Security in .Net
    Testing
    Web Services
    Windows Services
    Windows Controls
    WCF
    AJAX
    WPF
    XAML
    Reporting
    Setup
    VB.Net
    LINQ
    JQuery
    SilverLight
    JavaScript
    HTML5
    Crystal Report
    Cloud Computing
    Share Point
    Visual C++
    MVC
    Android
    PHP
    Java
    HTML
    WordPress
    Joomla
    Products
    Drupal
    Windows Phone
    JSON
    LightSwitch
    iPhone/iPad
    Ruby on Rails
    IIS 7
    Windows 8
    CSS/CSS3
    Excel
    MS Access
    Shortcut Keys
    Visual SourceSafe
    Team Foundation Server
    API(s)
    Sencha-Touch
    Single Page App
    Bootstrap
Follow Us
Follow _MindStick_ on Twitter View MindStick Software's LinkedIn profile View MindStick Software's Facebook profile
Top Contributor
Advertisement
Advertise with Us
Mindstick
Article Article  Forum Forum  Blog Blog  Quiz Quiz  Beginner Beginner  Careers Careers  Contact Contact  Login Login  
Home | Product | Services | About Us | Interview | DeveloperSection | Submit an Article | Submit Blog

Home >> Database >> Triggers in SQL Server
Triggers in SQL Server
Triggers in SQL Server


by Haider M Rizvi on 1/1/2011 8:45:11 PM

Views: 7540       Comments: 1

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.

Report Abuse Form
Reason:    
 


Trigger in SQL.
by Rohit Kesharwani 12/17/2011 8:50:57 AM
nice article. provide good information.
Report Abuse
Title :
Comment :
Text ColorBackground Color
BoldItalicUnderline
LeftCenterRightJustify
Ordered ListBulleted List
IndentOutdent
Horizontal Rule
SubscriptSuperscript
HyperlinkImage
Design ModeDesign
View HtmlHtml
     
 
Latest Article by Haider M RizviRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Latest BlogsRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Top Viewed ArticlesRSS Feed
    
    
    
    
    
    
    
    
    
    
Top Viewed BlogsRSS Feed
    
    
    
    
    
    
    
    
    
    
Latest Interview QuestionsRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Total Online Users: 5341
Advertisement
MindStick Cleaner
Advertise with Us
  
Copyright © 2014MindStick. All Rights Reserved.