|
|
You're in the
sql server programming
group:Recursive "Instead of" Trigger
sql server programming:
(2000 or 2005) and what update do you have of Books Online? Below is a quote from my 2005 Books Online, most recent refresh: If an INSTEAD OF trigger defined on a table executes a statement against the table that would ordinarily fire the INSTEAD OF trigger again, the trigger is not called recursively. Instead, the statement is processed as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions. For example, if a trigger is defined as an INSTEAD OF INSERT trigger for a table, and the trigger executes an INSERT statement on the same table, the INSERT statement executed by the INSTEAD OF trigger does not call the trigger again. The INSERT executed by the trigger starts the process of performing constraint actions and firing any AFTER INSERT triggers defined for the table. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi [quoted text, click to view] "Leila" <Leilas@hotpop.com> wrote in message news:O$FX651XHHA.2436@TK2MSFTNGP06.phx.gbl...
> Hi, > BOL states that "Instead Of" triggers can be fired recursively but I could not experience it by a > simple table and trigger. It works fine with After triggers. Are there any special consideration? > Thanks in advance, > Leila >
Hi,
BOL states that "Instead Of" triggers can be fired recursively but I could not experience it by a simple table and trigger. It works fine with After triggers. Are there any special consideration? Thanks in advance, Leila
Hi Tibor,
I could find the paragraph which you posted here in CREATE TRIGGER statement in my BOL. But this is what I think it implies recursion of Instead Of triggers: Both DML and DDL triggers are nested when a trigger performs an action that initiates another trigger or executes managed code by referencing a CLR routine, type, or aggregate. These actions can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels. You can control whether AFTER triggers can be nested through the nested triggers server configuration option. INSTEAD OF triggers (only DML triggers can be INSTEAD OF triggers) can be nested regardless of this setting. My BOL is April 2006, SQL Server 2005 Developer Edition SP2. Thanks "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message news:eyInQ91XHHA.1296@TK2MSFTNGP02.phx.gbl... [quoted text, click to view] > Can you point to where BOL states that instead of triggers fires > recursively? Also, what version (2000 or 2005) and what update do you have > of Books Online? Below is a quote from my 2005 Books Online, most recent > refresh: > > If an INSTEAD OF trigger defined on a table executes a statement against > the table that would ordinarily fire the INSTEAD OF trigger again, the > trigger is not called recursively. Instead, the statement is processed as > if the table had no INSTEAD OF trigger and starts the chain of constraint > operations and AFTER trigger executions. For example, if a trigger is > defined as an INSTEAD OF INSERT trigger for a table, and the trigger > executes an INSERT statement on the same table, the INSERT statement > executed by the INSTEAD OF trigger does not call the trigger again. The > INSERT executed by the trigger starts the process of performing constraint > actions and firing any AFTER INSERT triggers defined for the table. > > > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "Leila" <Leilas@hotpop.com> wrote in message > news:O$FX651XHHA.2436@TK2MSFTNGP06.phx.gbl... >> Hi, >> BOL states that "Instead Of" triggers can be fired recursively but I >> could not experience it by a simple table and trigger. It works fine with >> After triggers. Are there any special consideration? >> Thanks in advance, >> Leila >> >
I see what you mean.
In this context, nested triggers refer to you modify table A, it has a trigger that modifies table B. If table B has a trigger, would it fire? In other words, nesting refers to A modifies B, which modifies C, which modifiers D etc. Where recursive means A modifies A which modifies A which modifies A. Or, A modifies B which modifies A which modifies B etc. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ [quoted text, click to view] "Leila" <Leilas@hotpop.com> wrote in message news:eGn9m1GYHHA.1296@TK2MSFTNGP02.phx.gbl... > Hi Tibor, > I could find the paragraph which you posted here in CREATE TRIGGER statement in my BOL. But this > is what I think it implies recursion of Instead Of triggers: > > Both DML and DDL triggers are nested when a trigger performs an action that initiates another > trigger or executes managed code by referencing a CLR routine, type, or aggregate. These actions > can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels. You > can control whether AFTER triggers can be nested through the nested triggers server configuration > option. INSTEAD OF triggers (only DML triggers can be INSTEAD OF triggers) can be nested > regardless of this setting. > > My BOL is April 2006, SQL Server 2005 Developer Edition SP2. > > Thanks > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message > news:eyInQ91XHHA.1296@TK2MSFTNGP02.phx.gbl... >> Can you point to where BOL states that instead of triggers fires recursively? Also, what version >> (2000 or 2005) and what update do you have of Books Online? Below is a quote from my 2005 Books >> Online, most recent refresh: >> >> If an INSTEAD OF trigger defined on a table executes a statement against the table that would >> ordinarily fire the INSTEAD OF trigger again, the trigger is not called recursively. Instead, the >> statement is processed as if the table had no INSTEAD OF trigger and starts the chain of >> constraint operations and AFTER trigger executions. For example, if a trigger is defined as an >> INSTEAD OF INSERT trigger for a table, and the trigger executes an INSERT statement on the same >> table, the INSERT statement executed by the INSTEAD OF trigger does not call the trigger again. >> The INSERT executed by the trigger starts the process of performing constraint actions and firing >> any AFTER INSERT triggers defined for the table. >> >> >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> "Leila" <Leilas@hotpop.com> wrote in message news:O$FX651XHHA.2436@TK2MSFTNGP06.phx.gbl... >>> Hi, >>> BOL states that "Instead Of" triggers can be fired recursively but I could not experience it by >>> a simple table and trigger. It works fine with After triggers. Are there any special >>> consideration? >>> Thanks in advance, >>> Leila >>> >> > >
Thanks for clarification!
"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message news:%23cNjkrIYHHA.1296@TK2MSFTNGP02.phx.gbl... [quoted text, click to view] >I see what you mean. > > In this context, nested triggers refer to you modify table A, it has a > trigger that modifies table B. If table B has a trigger, would it fire? In > other words, nesting refers to A modifies B, which modifies C, which > modifiers D etc. > > Where recursive means A modifies A which modifies A which modifies A. Or, > A modifies B which modifies A which modifies B etc. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "Leila" <Leilas@hotpop.com> wrote in message > news:eGn9m1GYHHA.1296@TK2MSFTNGP02.phx.gbl... >> Hi Tibor, >> I could find the paragraph which you posted here in CREATE TRIGGER >> statement in my BOL. But this is what I think it implies recursion of >> Instead Of triggers: >> >> Both DML and DDL triggers are nested when a trigger performs an action >> that initiates another trigger or executes managed code by referencing a >> CLR routine, type, or aggregate. These actions can initiate other >> triggers, and so on. DML and DDL triggers can be nested up to 32 levels. >> You can control whether AFTER triggers can be nested through the nested >> triggers server configuration option. INSTEAD OF triggers (only DML >> triggers can be INSTEAD OF triggers) can be nested regardless of this >> setting. >> >> My BOL is April 2006, SQL Server 2005 Developer Edition SP2. >> >> Thanks >> >> >> >> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote >> in message news:eyInQ91XHHA.1296@TK2MSFTNGP02.phx.gbl... >>> Can you point to where BOL states that instead of triggers fires >>> recursively? Also, what version (2000 or 2005) and what update do you >>> have of Books Online? Below is a quote from my 2005 Books Online, most >>> recent refresh: >>> >>> If an INSTEAD OF trigger defined on a table executes a statement against >>> the table that would ordinarily fire the INSTEAD OF trigger again, the >>> trigger is not called recursively. Instead, the statement is processed >>> as if the table had no INSTEAD OF trigger and starts the chain of >>> constraint operations and AFTER trigger executions. For example, if a >>> trigger is defined as an INSTEAD OF INSERT trigger for a table, and the >>> trigger executes an INSERT statement on the same table, the INSERT >>> statement executed by the INSTEAD OF trigger does not call the trigger >>> again. The INSERT executed by the trigger starts the process of >>> performing constraint actions and firing any AFTER INSERT triggers >>> defined for the table. >>> >>> >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://sqlblog.com/blogs/tibor_karaszi >>> >>> >>> "Leila" <Leilas@hotpop.com> wrote in message >>> news:O$FX651XHHA.2436@TK2MSFTNGP06.phx.gbl... >>>> Hi, >>>> BOL states that "Instead Of" triggers can be fired recursively but I >>>> could not experience it by a simple table and trigger. It works fine >>>> with After triggers. Are there any special consideration? >>>> Thanks in advance, >>>> Leila >>>> >>> >> >> > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |