?
Solved

Recursive Trigger example needed in SQL Server !

Posted on 2009-07-16
6
Medium Priority
?
557 Views
Last Modified: 2012-05-07
Hi,

Can you please provide me basic example for "Recursive Trigger" in SQL Server ? Please provide me simple example with explanation in simple terms so that i can understand it easily.

Thanks
0
Comment
Question by:milani_lucie
[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
  • 3
  • 2
6 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1040 total points
ID: 24869171
Here is a good example of one from:
http://www.sqlmag.com/Articles/ArticleID/23123/23123.html?Ad=1

Note 'nested triggers' and 'recursive triggers' are just indicating that when a trigger is fired others may be as well.  In the case of a recursive trigger it is firing itself over and over, so can think of recursive as a specific kind of nested trigger.

http://msdn.microsoft.com/en-us/library/ms190739.aspx

ALTER DATABASE <db_name> SET RECURSIVE_TRIGGERS ON
GO
 
CREATE TRIGGER trg_d_Tree ON Tree FOR DELETE
AS
 
-- this will stop execution when no more children found
IF @@rowcount = 0 RETURN
 
-- notice here that you DELETE from same table this trigger is for
-- therefore, this trigger will fire again
DELETE FROM T
FROM Tree AS T JOIN deleted AS D
  ON T.parentid = D.childid
GO

Open in new window

0
 

Author Comment

by:milani_lucie
ID: 24871798
So i have this table:

CREATE TABLE Tree
(
  childid  int NOT NULL PRIMARY KEY,
  parentid int NULL
)
GO

INSERT INTO Tree VALUES(1, NULL)
INSERT INTO Tree VALUES(2, 1)
INSERT INTO Tree VALUES(3, 1)
INSERT INTO Tree VALUES(4, 1)
INSERT INTO Tree VALUES(5, 2)
INSERT INTO Tree VALUES(6, 2)
INSERT INTO Tree VALUES(7, 5)
INSERT INTO Tree VALUES(8, 5)
GO

Now can you please explain to me if I delete the row containing childid = 2, how the rows with childids 5, 6, 7, and 8 will be deleted as well ? Please explain to me in simple terms.

Thanks
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24871938
Ok, so you start out issuing a statement like:

DELETE FROM Tree
WHERE childid = 2;

Which fires your trigger, trg_d_Tree.

With recursive trigger support, the trg_d_Tree is allowed to call itself; therefore, when the delete statement in the trigger gets executed it not only deletes records with childid 5 and 6 but also fires trigger again and deletes the children of 5 and 6 which are childid 7 and 8.  

The trigger actual will fire again; however, now there are no children of childid 7 and 8, so when the trigger is fired for the last time @@rowcount = 0 and the recursion stops.

Leaving us with 5 total rows affected.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:milani_lucie
ID: 24871995
Let us say you are deleting this record:

INSERT INTO Tree VALUES(2, 1)

-- ChildId = 2
-- ParentId = 1

DELETE FROM T FROM Tree AS T JOIN deleted AS D ON T.parentid = D.childid

This statement will give you these two rows:

INSERT INTO Tree VALUES(5, 2)
INSERT INTO Tree VALUES(6, 2)

It will delete these two rows. Now how the records

INSERT INTO Tree VALUES(7, 5)
INSERT INTO Tree VALUES(8, 5)

will also be deleted ? Do the DELETED magic table will have all the records that were deleted ? I think it will contain ONLY last record. Please correct me if i am wrong !

Thanks
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24872113
Each time the trigger is fired, the deleted table will only contain the record(s) which were just deleted.  Correct!

Therefore, you will see 1, then 2, and then another 2 records in deleted.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 960 total points
ID: 24878419
What mwvisa1 is saying is absolutely correct.

Each "layer" of delete is resolved through it's own "deleted" table.

You probably need to try it for yourself.


CREATE TABLE Tree 
(
  childid  int NOT NULL PRIMARY KEY,
  parentid int NULL
)
GO
 
CREATE TRIGGER trg_d_Tree ON Tree FOR DELETE
AS
IF @@rowcount = 0 RETURN
DELETE Tree FROM Tree INNER JOIN deleted AS D ON Tree.parentid = D.childid
GO
 
INSERT INTO Tree VALUES(1, NULL)
INSERT INTO Tree VALUES(2, 1)
INSERT INTO Tree VALUES(3, 1)
INSERT INTO Tree VALUES(4, 1)
INSERT INTO Tree VALUES(5, 2)
INSERT INTO Tree VALUES(6, 2)
INSERT INTO Tree VALUES(7, 5)
INSERT INTO Tree VALUES(8, 5)
GO
 
DELETE from tree where childid = 2
GO
 
SELECT * from Tree
GO
 
-- left with (1,NULL) and (3,1) and (4,1)

Open in new window

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

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