Overlapping Trigger Precedence

Hi,
What is the execution precedence if there exist more than one update, delete
or insert triggers
I mean if there are more than one type of trigger such as more than one
update trigger.
Also, Can we specify a precedence for them?
and last If we call an update statement in the same update trigger and for
the same table that has this trigger, Is the trigger considers itself, I
mean is it recurs or not, or is it disables itself for that execution period
?.
Thanks in advance...
Emre GuldoganThere is no guarantee as to what order they'll fire in.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Emre Guldogan" <ask me please...> wrote in message
news:e$KbhKMJFHA.656@.TK2MSFTNGP14.phx.gbl...
> Hi,
> What is the execution precedence if there exist more than one update,
delete
> or insert triggers
> I mean if there are more than one type of trigger such as more than one
> update trigger.
> Also, Can we specify a precedence for them?
> and last If we call an update statement in the same update trigger and for
> the same table that has this trigger, Is the trigger considers itself, I
> mean is it recurs or not, or is it disables itself for that execution
period
> ?.
> Thanks in advance...
> Emre Guldogan
>|||You can use sp_settriggerorder to define the first and last trigger to
execute. For the rest the order is undefined. If it matters to you then
put all the logic in one trigger.
David Portas
SQL Server MVP
--|||On Wed, 9 Mar 2005 18:11:48 +0200, "Emre Guldogan" <ask me please...>
wrote:

>What is the execution precedence if there exist more than one update, delet
e
>or insert triggers
>I mean if there are more than one type of trigger such as more than one
>update trigger.
>Also, Can we specify a precedence for them?
Hi Emre,
Already addressed by David and Adam.

>and last If we call an update statement in the same update trigger and for
>the same table that has this trigger, Is the trigger considers itself, I
>mean is it recurs or not, or is it disables itself for that execution perio
d
>?.
There are two options governing this:
1. Recursive triggers. If this is off, a trigger can't fire itself. So
if an update trigger on table AA performans an UPDATE AA command, that
specific trigger is not called. This disables only direct recursion: if
the update trigger on AA performs a DELETE FROM BB statement, and the
delete trigger on BB performs UPDATE AA, the trigger will be called
recursively.
The recursive triggers option is a database setting: it will affect all
triggers in the current database, but it won't affect other databases on
the same server. You use ALTER DATABASE to change it.
2. Nested triggers. If this is off, statements executed from a trigger
don't fire any trigger at all.
The nested triggers option is a server setting: it will affect all
triggers in all databases on the server. You use sp_configure to change
it.
Other options to prevent recursive execution of triggers for specific
triggers only are:
* TRIGGER_NESTLEVEL() - check this to find how many nested trigger
executions there currently are.
* TRGIGER_NESTLEVEL(object_ID('trigger_nam
e')) - check this to find how
many times the specified trigger is currently nested.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)