Follow by Email

Monday, April 1, 2013

Triggers in Sql Server

Triggers are activated whenever  Insert update delete activity is performed on any table.

Example : Suppose we want to log information about a user who is updating the table and also time of it when the table is being updated. This type of information we can log with the help of triggers.

Two types of triggers :
1. After(For) Triggers
2. Instead of Triggers

Types of Triggers:

1.After trigger :

After trigger gets activated when all the activity on the table has been performed. Lets take an example to view this type of trigger.

first i will create a table and insert some records into it

Now we will create trigger on emp table for insert operation. So whenever any insert operation is performed on table this trigger will get executed.

So, In this trigger whatever new name is inserted in emp table im inserting that new name into other table called capturedata table.
Along with the new name im also inserting current date in the capturedata table.

We will now insert record in emp table and check whether any record has been logged in capturedata table or not.

2.  Instead of trigger :

Instead of trigger is used when we want to manipulate each data which getting inserted , deleted or updated. Lets take a example to explain this:

suppose we want to delete all records from table except for chetan. Hence in instead of trigger we will write a condition where we will check if name is equal to chetan. if the name is chetan we will not delete that record and will raise a error for that " this record cannot be deleted" or if the name is other than chetan we will delete that record.

we will keep the emp table as it is and will now write  Instead of trigger on delete.

below picture shows how to create a Instead of trigger: