Saturday, June 02, 2007

Trigger - An Intro!

Trigger... it is an special stored procedure that cannot be called explicity by an application but get called on an event in the database. It is like a method we attach to an button click event in page.

Events are insert, update or delete data in a table. When one of these operation is called for in a given table, trigger fires for that table. Importantly, in a MS SQL Server, the trigger fires once for a given session and NOT FOR EACH AFFECTED ROW.

Triggers are useful to execute business logic which sometime cannot be managed completely by constraints or any other mechanism or is sometimes easier to accomplish as trigger. Triggers are kind of tricky to write and needs lot of care from developer to avoid any side effect and it sometimes becomes difficult to debug the code.

In general, it is not preffered to trigger, as they are not completely portable between database and it sometimes goes unnoticed or totally hidden and becomes nightmare to find some issue.

There are 2 types of trigger in MS SQL. One is after trigger and other one is instead of trigger. In SQL 2005, the trigger has been associated with table alter, create etc as well.

I shall write more about these trigger in detail later.