?
Solved

SQL triggers causing errors with other triggers

Posted on 2006-06-29
4
Medium Priority
?
557 Views
Last Modified: 2008-02-26
I have two tables, OldTree and NewTree, that are set up to represent a tree/hierarchy structure.  Both tables are very similar - the difference being that the NewTree table also has a PATH and DEPTH column, for the purposes of navigating the tree structure without recursion (please see http://www.sqlteam.com/item.asp?ItemID=8866 for more info).  My goal is to keep the NewTree table up to date with changes made to the OldTree table, so I set up a couple triggers.
 
The DELETE TRIGGER on OldTree deletes that row in NewTree as well, and changes the paths of the children of the deleted value appropriately.
The UPDATE TRIGGER on OldTree updates that row in NewTree as well, and changes the paths of the children appropriately.
The UPDATE TRIGGER on NewTree takes care of grandchildren's paths etc when a delete or update trigger is executed on the OldTree table.
 
Now, I also have an INSERT TRIGGER on table OldTree - which inserts some values in table NewTree, and also updates any children.  There seemed to be an issue with the insert trigger, because it activates the update trigger on NewTree and throws this error: [37000(217)[Microsoft][ODBC SQL Server Driver][SQL Server]Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).]
Any thoughts on why that might happen with OldTree’s insert trigger but OldTree’s update triggers seem to work okay?

To skirt the problem above, I added code to disable the update trigger on NewTree before the insert trigger changes anything, then enabled the update trigger when it was finished.  In our test environment, this worked fine, but when we moved it to our live system, we received errors because our live system is replicated.  Replication apparently does not allow ALTER TABLE (what I used to disable the update trigger).  Am I going down the wrong path at this point, or should I be able to achieve my goal without having to disable the existing triggers?
 
Here is some sample code that should create the structure I’m working with:

 -------------------------------------------------------------
CREATE TABLE NewTree
(parent varchar(10), child varchar(10), path varchar(250), depth smallint)
 
INSERT NewTree VALUES (null,  '001',  ''      , 0)
INSERT NewTree VALUES ('001', '002', '/001', 1)
INSERT NewTree VALUES ('002', '003', '/001/002', 2)
INSERT NewTree VALUES ('003', '004', '/001/002/003', 3)
INSERT NewTree VALUES ('001', '005', '/001', 1)
 
CREATE TABLE OldTree
(parent varchar(10), child varchar(10))
 
INSERT OldTree VALUES ('001','002')
INSERT OldTree VALUES ('002','003')
INSERT OldTree VALUES ('003','004')
INSERT OldTree VALUES ('001','005')
 
-- (there is no row for top accounts in the group-accounts table)
 
-- INSERT TRIGGERS
 
CREATE TRIGGER insertNewTree ON OldTree AFTER INSERT
AS
 
 -- alter table NewTree disable trigger all -- this doesn't work because of replication
 
 /* IF NEW ROW'S PARENT ACCOUNT DOES NOT EXIST AS A CHILD, THEN WE NEED TO CREATE IT FIRST*/
 IF NOT EXISTS (SELECT NewTree.child FROM NewTree, inserted WHERE NewTree.child = inserted.parent)
 BEGIN
  INSERT INTO
   NewTree (parent, child, path, depth)
  SELECT
   null,
   parent,
   '',
   0
  FROM
   inserted
 END
 
 /* CHECK IF INSERTED ROW ALREADY EXISTS in NEWTREE (THIS IS THE CASE WHEN A NEW TOP ACCOUNT IS INSERTED) */
 IF EXISTS (SELECT NewTree.child FROM NewTree, inserted WHERE NewTree.child = inserted.child)
  BEGIN
 
 
  UPDATE t1
  SET
   t1.parent = t2.parent,
   t1.path = t3.path + '/' + t2.parent
  FROM
   NewTree as t1,
   inserted as t2,
   NewTree as t3
  WHERE
   t1.child = t2.child
   AND
   t3.child = t2.parent
   
 
  /* UPDATE CHILDREN */
 
  UPDATE
   t1
  SET
   t1.path = t3.path + '/' + t2.parent + t1.path
  FROM NewTree as t1, inserted as t2, NewTree as t3
  WHERE
   SUBSTRING(t1.path, 2, 10) = t2.child
   AND
   t2.parent = t3.child
 
 END
 
 ELSE
 
 BEGIN
  /* INSERT NEW ROW IN NEWTREE TABLE */
 
  INSERT INTO
   NewTree (parent, child, path)
  SELECT
    t1.parent,
    t1.child,
    (t2.path + '/' + t1.parent)
  FROM  
    inserted as t1
    LEFT JOIN
    NewTree as t2 ON t2.child = t1.parent
 
 
 
 END
 

 /* set depth for all records */
 UPDATE NewTree
 SET depth = LEN(path)/11
  -- alter table enable trigger all
 
-- UPDATE TRIGGER
 
CREATE TRIGGER updateNewTree2 ON NewTree AFTER UPDATE
AS
  /* UPDATE CHILDREN */
 UPDATE
  t1
 SET
  t1.path = REPLACE(t1.path, t2.path,
   (t3.path + '/' + t5.parent)),
  t1.depth = LEN(t1.path)/11
 FROM NewTree as t1, deleted as t2, NewTree as t3, inserted as t4, inserted as t5
 WHERE
  SUBSTRING(t1.path,1,(LEN(t2.path + '/' + t2.child))) IN (t2.path + '/' + t2.child)
  AND
  t3.child = t4.parent
 
 IF EXISTS (SELECT * FROM inserted WHERE parent IS NULL)
  BEGIN
   /* if the parent is set to null, then the path should be empty */
   UPDATE t1
   SET t1.path = '', depth = 0
   FROM NewTree as t1, inserted as t2
   WHERE t1.child = t2.child
   
   /* update path of children */
   UPDATE t1
   SET t1.path = '/' + SUBSTRING(t1.path, CHARINDEX(t2.child, t1.path), LEN(t1.path)-CHARINDEX(t2.child, t1.path) + 1 )
   FROM NewTree as t1, inserted as t2
   WHERE CHARINDEX(t2.child, t1.path) > 0
  END
 ELSE
 
  BEGIN
   /* update path */
    UPDATE t1
   SET
    t1.path = t5.path + '/' + t2.parent,
    depth = LEN(t1.path)/11
   FROM
    NewTree as t1,
    inserted as t2,
    inserted as t3,
    deleted as t4,
    NewTree as t5
   WHERE
    t1.child = t4.child
    AND
    t5.child = t2.parent
  END
 IF EXISTS (SELECT * FROM deleted WHERE parent IS NULL)
  BEGIN
   UPDATE t1
   SET
    t1.path = '/' + t2.parent + t1.path
   FROM
    NewTree as t1,
    inserted as t2
   WHERE
    SUBSTRING(t1.path, 2, 10) = t2.child
  END
 
  UPDATE NewTree SET depth = LEN(path)/11

0
Comment
Question by:tihetal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 28

Expert Comment

by:imran_fast
ID: 17025770
>>error: [37000(217)[Microsoft][ODBC SQL Server Driver][SQL Server]Maximum stored procedure, function, trigger, >>or view nesting level exceeded (limit 32).]


View nesting level exceeded limit 32 this is the limitation we have in sql server 2005 stored procedure and triggers.
You cannot call a trigger within a trigger more than 32 times. depend upon the number of records you have it exceeds the number of nested level which is 32. You should create stored procedure logic to work around this.
0
 

Author Comment

by:tihetal
ID: 17030993
I could create some stored proc logic around it, but that wouldn't stop my insert trigger from triggering the update trigger, so the same problem would remain, right?  

Is there any way I can disable a trigger without having to do an ALTER TABLE table DISABLE TRIGGER ***?
0
 
LVL 26

Accepted Solution

by:
DireOrbAnt earned 500 total points
ID: 17065781
My recommendation is to put the trigger logic inside stored procedures to insert tree items.
Call your procs instead of using INSERTs and you won't have to deal with triggers.

It will make your code faster as well.

If you have a few stored procedures that deals with that, remove those triggers and make sure your apps are calling the procs.
0
 

Author Comment

by:tihetal
ID: 17074918
I created a stored procedure and after some tweaking it seems to be working...  

I did have to put that last line

"  UPDATE NewTree SET depth = LEN(path)/11 "

into an insert trigger on the NewTree:


CREATE TRIGGER insertNewTree ON NewTree AFTER INSERT
AS

      UPDATE t1
      SET t1.depth = LEN(t1.path)/11      
      FROM NewTree as t1, inserted as t2
      WHERE
      t1.child = t2.child


0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question