Recursive Trigger example needed in SQL Server !

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
milani_lucieAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
milani_lucieAuthor Commented:
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
Kevin CrossChief Technology OfficerCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

milani_lucieAuthor Commented:
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
Kevin CrossChief Technology OfficerCommented:
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
Mark WillsTopic AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.