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 >> Implementing Triggers in Sql Server
Implementing Triggers in Sql Server
Implementing Triggers in Sql Server


by Awadhendra Tiwari on 1/27/2011 4:07:36 PM

Views: 3986       Comments: 0

Implementing Triggers in Sql Server

When we want to resolve concurrency issue in database we can use concept of triggers. In Sql server various kinds of triggers can be used for different types of data manipulation operations. The Sql server supports the following types of triggers.

Ø  Data Modification Language (DML) triggers.

Ø  Data Definition Language (DDL) triggers

DML triggers

A DML triggers is fired when data in the underlying table if affected by DML statements such as Insert, Update, or Delete.  Whenever a trigger is fired in response to the insert, delete or update statement the Sql server creates two temporary tables, called magic tables. The magic table which is created is called Inserted and Deleted. These are virtual table and are similar in structure to the table on which the trigger is defined.

Depending on the operation that is performed by user the trigger is further categorized in two parts as:

Ø  Insert trigger: Is fired whenever an attempt is made to insert a new row in the table. When an insert statement is executed, a new row is added to both the trigger and the inserted tables.

Ø  Delete trigger: Is fired automatically whenever an attempt is made to delete a row from the trigger table. When a delete statement is executed, the specified rows from the trigger table are deleted and are added to the deleted table.

Ø  Update trigger: Is fired when an update statement is executed in the trigger table. It uses two logical tables for its operation, the deleted table that contains the original rows and the inserted table that stores the new row.

DDL triggers

A DDL trigger is fired whenever DDL statements such as create table or alter table are executed. DDL triggers can be used to perform administrative task such as database auditing.

Depending on the way in which triggers are fired, they are categorized as follows:

Ø  After trigger: The after trigger can be created on any table for the insert, update or delete operation just like other triggers. The after trigger is fired after the execution of DML operations for which it has been defined.

Ø  Instead of triggers: Whenever you want to perform an action on different table or views then we can use instead of triggers. It can be created both table as well as view.

Ø  Nested trigger: Nested triggers are fired because of action of other triggers. For example, you update a row from a table Student. A trigger on Student table update rows from Marks table. Because we are updating a row from table B, a trigger is executed on table B to record the deleted rows.

Ø  Recursive Triggers: Recursive triggers are similar of nested triggers. The difference between Recursive trigger and nested trigger is that recursive triggers provide support at database label while nested trigger is not. As name implies recursive triggers are called by itself.

Syntax for creating triggers

create trigger trigger_name on objectname

{for/after/instead of} {DDL eventtype} as

begin

     sqlstatement1

end

 

where

     create is a keyword used to create trigger

     trigger is a keyword which means a trigger is created

     {for/after/instead of} represent types of trigger that should be created

     DDL eventtype represents trigger condition and actions.

Example demonstrating the creation of trigger

Example 1: Creating an Insert Trigger

create trigger trgInsertStudent on Student

for insert

as

     declare @sage int

     select @sage=sage from inserted

     if(@sage < 18)

     begin

           print 'The age should be greater than or equal to 18. Hence can not be inserted'

           rollback transaction

     end

return

 

select * from Student

 

insert Student values('S0005','Alok',16,'Lucknow')

output:

The age should be greater than or equal to 18. Hence can not be inserted

Msg 3609, Level 16, State 1, Line 3

The transaction ended in the trigger. The batch has been aborted.

Example 2: Creating a Delete Trigger

create trigger trgDelteStudent on Student

for delete

as

     print 'Deletion of student record is not allowed'

     rollback transaction

return

 

delete from Student where sid='S0005'

Output: 

Deletion of student record is not allowed

Msg 3609, Level 16, State 1, Line 2

The transaction ended in the trigger. The batch has been aborted.

Example 3: Creating an Update Trigger

create trigger trgUpdateStudent on Student

for update

as

     if UPDATE (sage)

     begin

           declare @sage int

           select @sage=sage from Student

           if(@sage < 18)

           begin

                print 'The age of student could not be less than 18'

                rollback transaction

           end

     end

return

 

update Student set sage=17 where sid='S0003'

output: (1 row(s) affected)

Example 5: Creating an After trigger

create trigger trgInsertShift on Student

after insert as

print 'Record inserted successful'

output

Record inserted successful

 

(1 row(s) affected)

Altering trigger

As a database developer you might need to modify the logic of code behind the triggers. For implementing such type of task we need alter command.

Syntax for altering trigger

alter trigger triffername

{for\after} {evemttype} as

Sqlstatement

Example for altering trigger

alter trigger trgInsertShift on Student

after insert as

print 'Thnks for inserting record'

Deleting a Trigger

Use drop keyword for deleting any trigger

Example for deleting Trigger

drop trigger trgInsertShift

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 Awadhendra TiwariRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Latest BlogsRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Top Viewed ArticlesRSS Feed
    
    
    
    
    
    
    
    
    
    
Top Viewed BlogsRSS Feed
    
    
    
    
    
    
    
    
    
    
Latest Interview QuestionsRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Total Online Users: 3217
Advertisement
MindStick Cleaner
Advertise with Us
  
Copyright © 2009 - 2014MindStick. All Rights Reserved.