trigger in sql server

Question

can we create more than on insert trigger on a single table? if yes then how they execute and what exactly idea about for,After trigger and when they construct? if no then why?

Answer 1

You can definitely create more than 1 trigger on a SQL Server table.

If you don't do anything they will execute in whatever order the server prefers and that will NOT be the same order every time.

You can force some order logic so that up to 3 triggers will fire in the order you want. You can define a FIRST and LAST trigger. Which gives you a max of 3 ordered triggers, because the one without the FIRST or LAST defined will fire in the middle. You can also do a Update/Insert/Delete Replace trigger or a trigger that takes place after the event instead with the AFTER keyword. Checkout these articles for more info and exact code:

• http://decipherinfosys.wordpress.com/2008/02/28/setting-the-execution-order-of-triggers-in-sql-server/
• http://msdn.microsoft.com/en-us/magazine/cc164047.aspx
• http://www.sql-server-performance.com/tips/trigger_tuning_p1.aspx

Finally, triggers in general slow down your database. This is especially true for SQL Server where they are considered to be a big speed killer. In general triggers should be used as sparingly as possible.