articles

Home / DeveloperSection / Articles / Implementing Triggers in Sql Server

Implementing Triggers in Sql Server

Anonymous User10576 27-Jan-2011

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/insteadof}{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/insteadof}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
forinsert
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'
           rollbacktransaction
     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
fordelete
as
     print'Deletion of student record is not allowed'
     rollbacktransaction
return
 
deletefrom Student wheresid='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
forupdate
as
     ifUPDATE (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'
                rollbacktransaction
           end
     end
return
 
update Student set sage=17 wheresid='S0003'


output: (1 row(s) affected)
Example 5: Creating an After trigger
create trigger trgInsertShift on Student
after insertas
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 insertas
print'Thnks for inserting record'
Deleting a Trigger

Use drop keyword for deleting any trigger

Example for deleting Trigger

droptrigger trgInsertShift



Updated 11-Oct-2019
I am a content writter !

Leave Comment

Comments

Liked By