Recursive trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Recursive trigger

Author

Topic

Yisman
Starting Member

8 Posts

Posted - 2008-10-05 : 13:42:24

Hi all,
I made a trigger that increments a column (SortOrder) by 1 when it meets certain criteria.
The idea is if someone has several connections (phones/cells) then if a user will update connection 1001 to SortOrder 1 while ConnectionID 1002 already has a SortOrder of 1, i would like ConnectionID 1002 to be updated as sortorder 2.
It works fine when there is only one record to be updated. the problem occurs if we have 3 or more records of connecions lets say SortOrders 1 2 3 & 4. then a user changes the 4 to a 2 then the trigger automatically pushes the original 2 to a 3, but it does not recurse to update the 3 to a 4.
am i understood?
how can we make the trigger run recursively until each row has a unique sortorder?

Thnk you very much for your input

This is the trigger:

ALTER TRIGGER [dbo].[trgSortOrder]
ON [dbo].[Connections]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
if update(personid) or update(sortorder)
begin
UPDATE Connections
SET Connections.SortOrder = Connections.SortOrder + 1
FROM Connections INNER JOIN
inserted AS I ON Connections.PersonID = i.personid
WHERE (connections.SortOrder = i.sortorder and connections.connectionid<>i.connectionid)
end
END

Yisman
Starting Member

8 Posts

Posted - 2008-10-05 : 15:33:29

Hi again
i found that to enable recursive triggers i need to execute

sp_dboption '<Database>', 'recursive triggers', TRUE

i did so and now it gives an error that it ecxeeds the 32 level limit.
this does not make sense as there are maximum 4 or 5 records per personid and the trigger joins Connections & Inserted on the PersonID field.

I appreciate your help and insight

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 00:45:51

no need of recursing and doing one update at a time. just go for set based update. do something like below after disbaling nested triggers

ALTER TRIGGER [dbo].[trgSortOrder]
ON [dbo].[Connections] 
AFTER INSERT,UPDATE
AS 
BEGIN
SET NOCOUNT ON;
if update(personid) or update(sortorder)
begin
UPDATE Connections
SET Connections.SortOrder = Connections.SortOrder + 1
FROM Connections INNER JOIN
inserted AS I ON Connections.PersonID = i.personid 
WHERE (connections.SortOrder >= i.sortorder 
and connections.connectionid<>i.connectionid)
end
END

Yisman
Starting Member

8 Posts

Posted - 2008-10-07 : 09:17:17

hi
thank you
i just (a few minutes ago) figd out that i can put in
IF @@rowcount = 0 RETURN

and since then all is well.

by the way, the code you put will probably add 1 even to a number that the sortorder is very high. i.e. if there are 4 records and one sortorder is set to 30 for some reason, then it will become 31, not so?
thank you very much for your help and advice
yisman

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 09:55:39

quote:
Originally posted by Yisman

hi
thank you
i just (a few minutes ago) figd out that i can put in
IF @@rowcount = 0 RETURN

and since then all is well.

by the way, the code you put will probably add 1 even to a number that the sortorder is very high. i.e. if there are 4 records and one sortorder is set to 30 for some reason, then it will become 31, not so?
thank you very much for your help and advice
yisman

yeah..thts true. good spot. however i think you can modify it like below

ALTER TRIGGER [dbo].[trgSortOrder]
ON [dbo].[Connections] 
AFTER INSERT,UPDATE
AS 
BEGIN
SET NOCOUNT ON;
if update(personid) or update(sortorder)
begin
UPDATE Connections
SET Connections.SortOrder = Connections.SortOrder + 1
FROM Connections INNER JOIN
inserted AS I ON Connections.PersonID = i.personid 
LEFT JOIN deleted d ON Connections.PersonID = d.personid 
WHERE (connections.SortOrder >= i.sortorder 
AND connections.SortOrder < d.sortorder 
and connections.connectionid<>i.connectionid)
end
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources