Solved

SQL triggers causing errors with other triggers

Posted on 2006-06-29
4
536 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
  • 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 250 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now