Can I have two different insert triggers on the same table?

Question

They seem to be allowed as I can see both my insert triggers listed under the table with different names. Is it common or a bad practice? I am using SQL Server 2005

Solution 1

Yes, you can definitely have more than one trigger for each operation, e.g. AFTER INSERT or AFTER UPDATE etc. It does make sense to split up separate concerns into separate, small, manageable chunks of code.

The one thing you cannot rely on is that they'll be executed in a certain order - the order in which the triggers are indeed executed also doesn't have to be stable, i.e. the same every time around.

Solution 2

it's a good practice because then you can break down your changes over time into little (agile) chunks and add or remove them independently of each other.

Comments

[Martin Smith]

What do the triggers do? The INSERTED table is not indexed so for performance reasons it might be better to consolidate them.

[Hammad Khan]

BTW if I insert 2 or more rows at a time (basically I am importing a bunch of records), is there a way I can reference EACH insert, or the trigger is automatically executed for every single insert?

[Andrew Lazarus]

Depends on RDBMS. Postgres guarantees multiple triggers are called in alpha order of name. They state SQL Standard (seldom followed) is in order of creation.

[marc_s]

@AndrewLazarus: I was referring to SQL Server (since the OP asked about that), and SQL Server doesn't have any such capability. Thanks for pointing that out!

[Andrew Lazarus]

Absolutely. I often add notes like that figuring people land here from Google a lot.

[Martin Smith]

Though you can use sp_settriggerorder to set first and last trigger.

[Niraj]

@hmd trigger will be automatically executed for every single insert untill and unless you are using bulk inserts in which trigger will be executed once for each bulk insert command.

[olf]

Know it is an old question! In your trigger you can always reference a table called "inserted" which contains all the rows you have just inserted (being one or more rows on an insert trigger). And by creating cursors you can loop through each of your rows and do stuff to them if you want.