cursor is not recursing - 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

General SQL Server Forums

New to SQL Server Programming

cursor is not recursing

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2014-11-29 : 21:49:34

Hi i have a simple cursor, to loop and exec a insert stored proc, the problem i have is the @Id does not get updated to the next id in the loop, i have never seen this before, i think it is because of a trigger i have set.

the loop works fine if in the update trigger i comment out the line below.
-- THIS CAUSES INFNATE LOOPS IF RUNNING EXEC ON INSERT SP INSIDE A LOOP

i just need to understand why the cursor is failing?

**The cursor**
**The cursor**
declare @aaId bigint
declare @aaDateNow datetime = getdate()
declare aacust_cursor01 cursor for
select Id
from tree
where Title = 'E-Commerce' and TreeObjectType = 'RootItem'

OPEN aacust_cursor01
FETCH NEXT FROM aacust_cursor01
INTO @aaId

WHILE @@FETCH_STATUS = 0
BEGIN
exec Tree_Insert @aaId, 'Discount Codes/Vouchers', 1, 0, 1, 'icon promo', 0, @aanewTempId, NULL, 'RootPromo', 1, 'Admin', @aaDateNow, 'Add/Edit/Delete Promotional Codes', '1|', 0, 0

FETCH NEXT FROM aacust_cursor01 INTO @aaId
END
CLOSE aacust_cursor01
DEALLOCATE aacust_cursor01

**the insert trigger**
**the insert trigger**
declare @TreeId bigint;
SELECT @TreeId = i.Id from inserted i;

--update any parents
WITH Emp_CTE AS (
select Id, ParentId, Title from tree where id = @TreeId
union all
select t.Id, t.ParentId, t.Title from tree t INNER JOIN Emp_CTE AS ecte ON ecte.ParentId = t.Id
)

--we may not need this now??
UPDATE Tree SET ChildModified = getdate() WHERE Id IN (SELECT Id FROM Emp_CTE)

-- Generate HierarchyId from parent's
DECLARE @parentId bigint
SELECT @parentId = i.ParentId FROM inserted AS i

----update the parent node
-- THIS CAUSES INFNATE LOOPS IF RUNNING EXEC ON INSERT SP INSIDE A LOOP
UPDATE Tree SET DateModified = getdate() WHERE Id = @parentId

DECLARE @parentHierarchyId hierarchyid
SELECT @parentHierarchyId = HierarchyId
FROM Tree
WHERE Id = @parentId

DECLARE @newHierarchyId hierarchyid
SET @newHierarchyId = hierarchyid::Parse(@parentHierarchyId.ToString() + CAST(@TreeId AS nvarchar(50)) + '/')

UPDATE Tree SET HierarchyId = @newHierarchyId where id = @TreeId and HierarchyId != @newHierarchyId

**the update trigger**
**the update trigger**
declare @TreeId bigint;
SELECT @TreeId = i.Id from inserted i;

--update any parents
WITH Emp_CTE AS (
select Id, ParentId, Title from tree where id = @TreeId
union all
select t.Id, t.ParentId, t.Title from tree t INNER JOIN Emp_CTE AS ecte ON ecte.ParentId = t.Id
)
UPDATE Tree SET ChildModified = getdate() WHERE Id IN (SELECT Id FROM Emp_CTE)

--Generate HierarchyId from parent's
DECLARE @parentId bigint
SELECT @parentId = i.ParentId FROM inserted AS i

DECLARE @parentHierarchyId hierarchyid
SELECT @parentHierarchyId = HierarchyId
FROM Tree
WHERE Id = @parentId

DECLARE @newHierarchyId hierarchyid
SET @newHierarchyId = hierarchyid::Parse(@parentHierarchyId.ToString() + CAST(@TreeId AS nvarchar(50)) + '/')

UPDATE Tree SET HierarchyId = @newHierarchyId where id = @TreeId

Mar
Starting Member

47 Posts

Posted - 2014-12-09 : 10:22:09

Your problem is using triggers incorrectly. You probably have a trigger causing your cursor to behave improperly.

Some people say NEVER use triggers.

I use them for very simple logging operations. Never anything more that a simple select or insert.

The way you are using triggers (business logic) causes the type of problem you are experiencing. You will have to debug all triggers used by all tables referenced by all procedures involved in the process you are having problems with. That is not an easy task nor one that can be done in a forum. Now you see why some people recommend NEVER to use triggers.

You should move your business logic to the appropriate library. If you need help with that I can help you.

If you insist on using triggers the way you want to I can't help you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources