May 13, 2004 at 5:09 am
This trigger below is based on self referencing table. The trigger will update MenuActive field setting it to 0 or 1 depending on updating action.
However - I get the error:
Server: Msg 16915, Level 16, State 1, Procedure SetParentAndChildActive, Line 12
A cursor with the name 'C1' already exists.
When running it. I thought the declared cursor was local only ?? It behaves as if the nested triggers uses same cursor object - and not just same name. ???
Recursive trigger setting is enabled on DB
Thanks,
Lars Kjeldsen
______________________________________________
CREATE TRIGGER SetParentAndChildActive ON dbo.Menuitem
FOR UPDATE
AS
SET NOCOUNT ON
DECLARE @MenuitemID Int
DECLARE @ParentID Int
DECLARE @MenuActive Int
DECLARE C1 CURSOR FOR
SELECT MenuitemID, ParentID, MenuActive FROM INSERTED
OPEN C1
FETCH NEXT FROM C1 INTO @MenuitemID, @ParentID, @MenuActive
WHILE @@FETCH_STATUS = 0
BEGIN
IF @MenuActive = 1
BEGIN
UPDATE Menuitem SET MenuActive = 1 WHERE MenuitemID = @ParentID
END
ELSE
BEGIN
UPDATE Menuitem SET MenuActive = 0 WHERE ParentID = @MenuitemID
END
END
CLOSE C1
DEALLOCATE C1
May 13, 2004 at 5:16 am
Found problem !
Declare cursor as LOCAL
DECLARE C1 CURSOR LOCAL FOR
!!! Better to write it out rather than trust default settings I guess 🙂
May 13, 2004 at 5:08 pm
Lars,
An even better way to declare the cursor would be to use a local variable.
DECLARE @c1 CURSOR
SET @c1 = CURSOR FOR...
Everything else is the same but you have now limited the scope just the same as any local variable.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
May 14, 2004 at 12:38 am
Gary, can you elaborate further why cursor variable is preferable to local cursor as Lars wrote? Aren't both similarly limited in scope?
May 17, 2004 at 1:08 pm
In effect they are the same as you stated. However you will never have a situation where you mistakenly forget to put "Local" on your declaration and end up with a global cursor and all of a sudden have a problem like Lars did above. I also find it easier to read the syntax as I'm so used to seeing my variables preceeded with the @ symbol.
I've been using the Local Variable syntax since SQL 7 first came out. At the time I was doing a lot of research into the differences between 6.5 and 7. This was one of the recommended changes at the time. I've never looked back and just do it that way by habit now.
The way I see it is that anytime you can, you should limit your variables scope. The local variable syntax is one way to ensure that you have limited the scope of a cursor.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
May 17, 2004 at 1:14 pm
Ok, good answer. Thanks.
May 18, 2004 at 12:52 am
Gary, Mike,
Thanks for your input. Have declared the cursor as local now - adding "@" prefix as other local variables - and it does give better overview. Moreover - doing this each time helps me not to redo the error I did in the first place.
Can any of you tell me the best way to declare the cursor as a "firehose" type - as we call it in ASP development. The recordset is to be run through forward only - and only once - in my example - just to put together a commaseperated string that is returned as an output parameter (scalar) - rather than entire resultset to be looped outside SQL Server.
For now I have declare it as "SET @C1 = CURSOR FAST_FORWARD FOR" - but I am qurious about if this type (FAST_FORWARD) really is the fastest type ?? The SQL Server documantation does nor point out clearly which to use in my case.
Thanks for great input,
Lars
May 18, 2004 at 10:25 am
Add "For Read Only" at the end of the cursor declaration...
DECLARE @C cursor
SET @C = cursor FOR
SELECT * FROM Foo
FOR READ ONLY
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply