Home > DeveloperSection > Articles > Implementing Triggers in Sql Server

Implementing Triggers in Sql Server

Posted by  Awadhendra Tiwari
on    January-27-2011 16:07 PM

Database Database 
Ratings:
0 Comment(s)
 4402  View(s)
Rate this:

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

     createis a keyword used to create trigger

     triggeris 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
















Recent Activities


Kamlakar Singh added new Blog Convert Text Document to PDF File   7 days ago

Kamlakar Singh added new Article Introduction to Backbone.js   7 days ago

Kamlakar Singh added new Question Explain the AdRotator Control.   7 days ago

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