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 >> Trigger in SQL Server
Trigger in SQL Server
Trigger in SQL Server


by Sachindra Singh on 2/14/2011 4:41:42 PM

Views: 4667       Comments: 0

Trigger in SQL Server

A Trigger is a special kind of stored procedure that is invoked whenever data in the underlying table is affected by any of the Data Manipulation Language (DML) statements -INSERT, UPDATE OR DELETE or Data Definition Language (DDL).A Trigger is a block of code that constitutes a set of T-SQL statements activated in response to certain actions, such as insert or delete .Triggers are used to ensure data integrity before or after performing data manipulations.
Kinds of Triggers

·         Data Modification Language (DML) triggers

·         Data Definition Language (DDL) triggers

Data Modification Language (DML) triggers

Data Modification Language triggers is fired whenever data in the underlying table is affected by any of the Data Manipulation Language (DML) statements -INSERT, UPDATE OR DELETE.DML trigger is use to maintaining consistent, reliable and correct data in tables.

DML triggers characteristics:

·         Fired automatically by the SQL Server

·         Trigger cannot explicitly invoked or executed

·         It cannot return data to the user.

Whenever trigger fired two tables are created:

·         Inserted

·         Deleted

The Inserted table contains a copy of all records that are inserted in the trigger table. The Deleted table contains all records that have been deleted from the trigger table. This table is used to refer old values.
Whenever any updating takes place, the trigger uses both the inserted and deleted tables.


Insert trigger: Is fired whenever an attempt is made to insert a row in the trigger table.

Delete trigger:  Is fired whenever an attempt is made to delete a row in the trigger table.

Update trigger: Is fired when Update statements executed in the trigger table .It uses two logical tables for its operations, the deleted table that contains the original rows and the inserted table that stores the new rows.

Data Definition Language (DDL) triggers

A Data Definition Language trigger is fired in response to Data Definition Language, such as create table or alter table .DDL triggers can be used to perform database auditing, administrative tasks.

DDL trigger are categorized as:

·         After Triggers

·         Instead of Triggers

·         Nested Triggers

·         Recursive Triggers

After Trigger

It is a specialized stored procedure that is executed when data in the table associated with trigger is modified. After trigger executes the code associated with it after the event for which it is made happens. You can have multiple after triggers for any single DML operations.

Instead of Trigger

The instead of triggers can be primarily used to perform an action , such as a DML operation on another table or view .This type of trigger can be created on both a table as well as view .

Nested Trigger

Both DML and DDL triggers are nested when a trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels. You can control whether AFTER triggers can be nested through the Nested Triggers server configuration option. INSTEAD OF triggers (only DML triggers can be INSTEAD OF triggers) can be nested regardless of this setting.

Recursive Triggers

Recursive triggers when a trigger fires and performs a statement that will cause the same trigger to fire, recursion will occur. There are two triggers, Direct and Indirect.

How to Create Trigger

Create Trigger trigger_name

      On {Object Name}

{For | After | Instead Of}{event _type[,..n]| DDl_Database_Level_Events}

      {AS

            {

            SQl_Staement[..n]

            }

For example I have one table Studentdetail to perform trigger operation

StudentDetail

Trigger in SQL Server

Creating Insert Trigger

Query

create trigger trg2

on StudentDetail

for insert

as

begin

      select * from StudentDetail

end

In StudentDetail inserting one new record after created insert trigger

Query

Insert StudentDetail values('S011','Imran','25','Varansi','U.P.')

Output

Trigger in SQL Server

Creating Delete trigger

Query

create trigger trg4

on StudentDetail

for delete

as

begin

      select * from StudentDetail

end

In Studentdetail table deleting one record after created delete trigger

Query

delete StudentDetail where Name='Imran'

Output

Trigger in SQL Server

Creating Update Trigger

Query

create trigger trg5

on StudentDetail

for update

as

begin

      select * from StudentDetail

end

In studentdetail table updating one record after created update trigger

Query

update StudentDetail set City='Gorakhpur',State='U.P.' where Name='Pooja'

Output

Trigger in SQL Server

Creating After Trigger

Query

create trigger trg10

on StudentDetail

After

insert

as

begin

select * from StudentDetail

end

In studentdetail table inserting one record after created after trigger

Query

Insert StudentDetail values('S012','Vivek','25','Varansi','U.P.')

Output

Trigger in SQL Server


Creating Instead of Trigger

Query

create trigger trg12 on StudentDetail

 instead of update as

begin

    select * from StudentDetail

end

In studentdetail table updating one record after created instead of trigger but you will see there is no updating performs on table there is cause of instead of trigger.

Query

update StudentDetail set City='Jaunpur' where Name='Raj'

Output

Trigger in SQL Server

 

 

Report Abuse Form
Reason:    
 

Title :
Comment :
Text ColorBackground Color
BoldItalicUnderline
LeftCenterRightJustify
Ordered ListBulleted List
IndentOutdent
Horizontal Rule
SubscriptSuperscript
HyperlinkImage
Design ModeDesign
View HtmlHtml
     
 
Latest Article by Sachindra SinghRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Latest BlogsRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Top Viewed ArticlesRSS Feed
    
    
    
    
    
    
    
    
    
    
Top Viewed BlogsRSS Feed
    
    
    
    
    
    
    
    
    
    
Latest Interview QuestionsRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Total Online Users: 3064
Advertisement
MindStick DataConver
Advertise with Us
  
Copyright © 2009 - 2014MindStick. All Rights Reserved.