What is Trigger ? A Trigger is a special type ofstored procedure which execute based on some event fired. Triggers are attached with some Tables, Database or Server and registered with some events, when ever that event fire, trigger will execute automatically and do the predefine job. As I have already mention execution of trigger is based on some events, so we cannot run trigger manually. 
Trigger can execute on data modification using Insert, Delete, Update command or it can execute some data definition change like Table creation etc. We can also create trigger on server level which will execute based on some event perform on server. Some times trigger can cause execution of other trigger- know as "Nested Trigger" General Syntax of Creating Trigger is To summarize the whole things - Triggers are special type of Stored procedure which executed based on some events.
- We cannot execute trigger manually.
- We can have trigger on Table Level, Database Level and Server Level
- We can not use parameter with trigger
- we cannot use any return value from Trigger.
Now lets have a look on the advantages and disadvantages of Trigger. Advantages of Trigger Trigger has the following advantages - Trigger invoked based on some events.
- Trigger can be fired on database and also server event
- It is very helpful to check some complex condition where Constraints failed.
- It is used for database table auditing
- Triggers can be used to enforce constraints on Table.
Disadvantages of Trigger - Triggers that are created on table are invisible, so its very difficult to maintain them.
- Triggers run every time when the database fields are updated/Insert/delete and it is overhead on system.
- we cannot use parameter with trigger
- Trigger can not return any value
Different Types of Trigger As I have already discuss, trigger can be applicable in Table, Database or in Database Server. Based on that, we can categorize trigger in following way. 
SQL Sever command divided into two major category DML ( Data Manipulation Language ) and DDL (Data Definition Language) . DML statement are usually work with Database Table like insert update and Delete. DML trigger are those trigger which are execute when some DML statement fired on a table. DDL statements are used to build and modify the structure of your tables and other objects in the database. In SQL Server 2005 we have another Trigger called CLR Trigger. Now coming back to the details, DML Trigger : This is based on Table. DML trigger are execute when any of the following DML statement fired on that particular Table. Those are Insert
Delete
Update
we can attach a DML Trigger with any table against of those (Insert, delete, update ) SQL Command. we can have the DML Trigger for View also. We can create multiple trigger on a single event . In that case we have to specify the sequence of execution. If have discussed it in later. There are Two types of DML Trigger AFTER Trigger
INSTEAD OF Trigger
Note : This is the end if Part 1, In the net article i will example all the DML and DDL trigger with example. Hope this will give you a very good basic idea of Data Base Trigger. Thank you Jana |