Powered by Blogger.

Saturday, February 15, 2014

Triggers in sql server 2008

In this post we will discuss about Triggers in sql server. Also you can check my previous posts on:

- Error functions in sql server

- Null Coalescing Operator in C#.Net

- Activation Models in Remoting in C#.Net

Triggers are the stored sub programs that will be executed automatically based on the specified event.

Based on the event specified by the trigger, triggers are classified in to DDL triggers and DML triggers.

When a trigger is created by specifying a DDL command as event then it is called as DDL trigger. DDL trigger is new in Sql server 2005.

When a trigger is created by specifying a DML command as event then it is called as DML trigger.

DML trigger has the following 3 main purposes.

- Create procedural integrity constraint
- Record auditing information of a table
- Allow insert, update and delete on complex view.

Creating  DML triggers:
To create a trigger use the create trigger command. Below is the syntax for creating a DML trigger.
                                Create trigger <triggername>
                               On <table/view name>
                               [with encryption]
                              For/after/instead of [insert][,][update][,][delete]

Before and After triggers:
In oracle we have before and after triggers  where the before triggers are executed before executing insert, update and delete statements on table and where the after triggers are executed after executing insert, update and delete statements on table.

But in sql server does not support before triggers and every trigger you create in sql server using the keyword for/ after while specifying the event will be an after trigger.

Create a trigger on emp table for not allowing the users to perform insert, update and delete on Sunday and before 9 A.M or after 6 p.m on regular days.

Create trigger NotAllowDML
On Emp for insert,update,delete  as
Declare @day int,@hour int
Set @day=datepart(dw,getdate())
Set @hour=datepart(hh,getdate())
If @day=1 or @hour<9 or @hour>17
Rollback tran
Print ’DML operations are not allowed at this time’

Triggers and Transactions:
When there is a trigger on table then a transaction will be created implicitely whether or not "implicit_transactions" operation os set to "ON" and that transaction can be accessed only with in the trigger. When there is no "rollback transactions" statement with in the trigger then the transaction will be automatically committed and otherwise it will be rollback.