Truth Behind Triggers [MS SQL Server]

I am going to discuss here about some hidden concepts of Triggers in SQL Server, mainly After Triggers.  Suppose you have a Table named as Customer in Sales database and lets create a after insert trigger called test on Customer Table. and inside this trigger lets write a query to insert data in the Customer Table itself. Now think what will happen when you will try to insert data in the Table Customer? So lets have a look on the general definition of the trigger : A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the required action no the table that they are assigned to .

Lest create the trigger using the following code : USE AdventureWorks2012; GO IF OBJECT_ID ('Sales.Test', 'TR') IS NOT NULL DROP TRIGGER Sales.Test; GO CREATE TRIGGER Test ON Sales.Customer AFTER INSERT AS BEGIN INSERT INTO Sales.Customer VALUES (Value1,Value2,Value .... ) END GO Now lets execute a query  which will insert a row in the table Customer as follows. USE AdventureWorks2012; GO INSERT INTO Sales.Customer VALUES (Value1,Value2,Value .... ) Now think what should happen ideally as per the definition of the AFTER TRIGGER? As per the definition it should go to infinite loop as it will insert row again and again but it will not happen. The above query will insert 2 rows in the table. The first row through the query and second row through the trigger query. Note that the insert statement inside the trigger will not fire the trigger again.   Reference : http://msdn.microsoft.com/en-us/magazine/cc164047.aspx