Triggers in Microsoft SQL Server

Triggers are nothing but the special stored procedure which used to fire automatically with table events like INSERT, UPDATE, and DELETE. Apart from traditional Instead of and after trigger, Microsoft SQL Server 2005 became rich with DDL Triggers. You can have a look at DDL Triggers at:

http://www.sqlhub.com/2009/03/ddl-trigger-in-sql-server-2005-for.html

The main reason now I am writing Trigger article is, one should have concept of transaction flow, T-SQL, Stored Procedure of SQL Server etc. I finished many articles for said topic and felt this is the right time to introduce Triggers in my blog.

If you are aware with ORACLE than you might know that ORACLE used to fire trigger per operation and per row but SQL Server fires up the trigger once for every data modification not for affected row only. This is the thing I like most about SQL Server’s trigger.

As per my first paragraph, SQL Server has two types of DML trigger available Instead of trigger and after trigger. Both are completely different than each other in every aspect.

Before we start writing the trigger practically, it would be interesting and useful to understand when and how trigger will fire? To understand this, let us see transaction flow of SQL Server.

-- Identity insert always gets priority
-- Nullability Constraint
-- Data-type check
--DML command like INSERT, UPDATE, and DELETE stops execution and Instead Of trigger will fire
-- Primary-Key constraints
-- Check Constraints
-- Foreign-Key constraints
-- Hold DML finishes
-- After Trigger
-- COMMIT trans
-- finally writes the data file.

In short, when you executes any DML statement, it will first check Identity, nullability and data type check than performs Instead Of trigger. Once SQL Server done with these, it will run After Trigger before committing the transaction.

I will come up with example of triggers in my next articles.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com