Two triggers on one table sql server

Question

I have two triggers on one of the tables in my database. One is for insert trigger and the other is an after update trigger. The insert trigger will update the table with values. My question is this; Is it possible that that the update trigger is firing at the same time that the insert is doing its job?

Answer 1

If your insert trigger does an update to the table, the update trigger will be called. If a trigger triggers another trigger, it's called a "recursive trigger".

Recursive triggers can be disabled for an entire server:

sp_configure 'nested_triggers', 0
go
reconfigure

Or for one database:

alter database yourdb set recursive_triggers off

Answer 2

A FOR INSERT trigger will fire only on INSERT statements. A FOR UPDATE trigger will fire only on UPDATE statements. Of course, if your insert trigger executes any UPDATE statements then it will fire the update trigger, and vice versa.

Your UPDATE trigger won't fire for an INSERT statement (excepting the update-within-trigger case above), but of course you still have to design for concurrency, since it's possible for two different users to be running two different operations at the same time - one INSERT and one UPDATE.