Recursive (Instead Of) Trigger
in [Programming SQL]
|
From: Bill Cohagan on 22 Apr 2010 10:27 I've got a recursive Instead Of Delete trigger which I'm forced to use due to SQL 2005/2008 prohibiting cycles in cascade operations. I'm getting a (surprising) RI error when I single step through the trigger body and reach the point of the recursive Delete operation. Even though I do a "Step Into" I immediately get the error without reentering at the top of the trigger code. This leads me to believe that the Instead Of Trigger is NOT being invoked on the recursive call. That would explain both the RI error *and* that Step Into doesn't behave as expected. I've reviewed several threads here regarding recursive triggers and saw one "offhand" remark that Instead Of triggers are never invoked recursively, but no details were offered. What I'm attempting is to delete a subree in a table which contains parent pointers. So, I'd appreciate any help regarding what is to be expected with recursion in Instead Of triggers and/or how best to implement this sort of thing in general. Thanks in advance, Bill
From: --CELKO-- on 22 Apr 2010 11:41 >> What I'm attempting is to delete a subtree in a table which contains parent pointers .. or how best to implement this sort of thing in general.<< Use the Nested Sets model instead of mimicking pointer chains in SQL. No need for recursive procedural code, just use a single declarative DELETE FROM statement and forget about triggers. You might also get a copy of TREES & HIERARCHIES IN SQL for more code and other (more relational) ways to do this.
From: Tony Rogerson on 22 Apr 2010 12:19 Hi Bill, Can you post your code and we'll take a look. Many thanks, Tony. "Bill Cohagan" <BillCohagan(a)discussions.microsoft.com> wrote in message news:E6D0D7A2-8407-4751-8BB7-813F7A25905E(a)microsoft.com... > I've got a recursive Instead Of Delete trigger which I'm forced to use due > to > SQL 2005/2008 prohibiting cycles in cascade operations. I'm getting a > (surprising) RI error when I single step through the trigger body and > reach > the point of the recursive Delete operation. Even though I do a "Step > Into" I > immediately get the error without reentering at the top of the trigger > code. > This leads me to believe that the Instead Of Trigger is NOT being invoked > on > the recursive call. That would explain both the RI error *and* that Step > Into > doesn't behave as expected. > > I've reviewed several threads here regarding recursive triggers and saw > one > "offhand" remark that Instead Of triggers are never invoked recursively, > but > no details were offered. > > What I'm attempting is to delete a subree in a table which contains parent > pointers. > > So, I'd appreciate any help regarding what is to be expected with > recursion > in Instead Of triggers and/or how best to implement this sort of thing in > general. > > Thanks in advance, > Bill
From: Tony Rogerson on 22 Apr 2010 12:25 > Use the Nested Sets model instead of mimicking pointer chains in SQL. > No need for recursive procedural code, just use a single declarative > DELETE FROM statement and forget about triggers. You might also get a > copy of TREES & HIERARCHIES IN SQL for more code and other (more > relational) ways to do this. The nested sets model does not scale compared to other solutions. To remove nodes not only do you have to delete the node rows but you also have to update the left/right columns to "fix" the order that the nested sets model relies on (hardly relational!). Whilst other methods rely on pointer chains - the nested set relies on ordering down the nodes. A good reference point with many examples is here: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Concurrency problems are abound with a nested sets implementation that has even a slightly volatile hierarchy. --ROGGIE-- "--CELKO--" <jcelko212(a)earthlink.net> wrote in message news:94ef22de-c89a-4e36-8233-02be7e11998f(a)h16g2000prf.googlegroups.com... >>> What I'm attempting is to delete a subtree in a table which contains >>> parent pointers .. or how best to implement this sort of thing in >>> general.<< > > Use the Nested Sets model instead of mimicking pointer chains in SQL. > No need for recursive procedural code, just use a single declarative > DELETE FROM statement and forget about triggers. You might also get a > copy of TREES & HIERARCHIES IN SQL for more code and other (more > relational) ways to do this.
From: Scott Morris on 22 Apr 2010 12:27
"Bill Cohagan" <BillCohagan(a)discussions.microsoft.com> wrote in message news:E6D0D7A2-8407-4751-8BB7-813F7A25905E(a)microsoft.com... > I've got a recursive Instead Of Delete trigger which I'm forced to use due > to > SQL 2005/2008 prohibiting cycles in cascade operations. I'm getting a > (surprising) RI error when I single step through the trigger body and > reach > the point of the recursive Delete operation. Even though I do a "Step > Into" I > immediately get the error without reentering at the top of the trigger > code. > This leads me to believe that the Instead Of Trigger is NOT being invoked > on > the recursive call. That would explain both the RI error *and* that Step > Into > doesn't behave as expected. > > I've reviewed several threads here regarding recursive triggers and saw > one > "offhand" remark that Instead Of triggers are never invoked recursively, > but > no details were offered. This is exactly what BOL states (ref: create trigger tsql statement): ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/edeced03-decd-44c3-8c74-2c02f801d3e7.htm 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. |