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
     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
     declare @sage int
     select @sage=sage from inserted
     if(@sage < 18)
           print 'The age should be greater than or equal to 18. Hence can not be inserted'
           rollback transaction
select * from Student
insert Student values('S0005','Alok',16,'Lucknow')

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
     print 'Deletion of student record is not allowed'
     rollback transaction
delete from Student where sid='S0005'

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
     if UPDATE (sage)
           declare @sage int
           select @sage=sage from Student
           if(@sage < 18)
                print 'The age of student could not be less than 18'
                rollback transaction
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'

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
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

  Modified On Nov-28-2017 10:23:55 PM

Leave Comment