Declaring a Cursor within a Trigger

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 2000 Forums

SQL Server Development (2000)

Declaring a Cursor within a Trigger

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-15 : 08:26:44

Rauland writes "I'm trying to execute the trigger below. The syntax checks out fine, but I'm not getting the expected result of the insert. What I'm doing wrong?

CREATE TRIGGER AddRec_A120Main
ON A120T
FOR UPDATE

AS
DECLARE @PINASSIGNED VARCHAR (6)
select @PINASSIGNED = PinAssigned from A120T

DECLARE ENTRY1 CURSOR FOR
SELECT PinAssigned
FROM A120T
WHERE PINASSIGNED = @PINASSIGNED
ORDER BY PINASSIGNED

OPEN ENTRY1

IF (@PINASSIGNED IS NOT NULL)

FETCH NEXT FROM ENTRY1
INTO @PINASSIGNED

WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN
INSERT INTO A120Main
(AppType,
ReqType,
NwPin,
NwUserId,
PIN)
SELECT
AppType,
ReqType,
NwPin,
NwUserId,
PIN,
FROM A120T WHERE PINASSIGNED = @PINASSIGNED

-- Get the next ENTRY.
FETCH NEXT FROM ENTRY1
INTO @PINASSIGNED
END

CLOSE ENTRY1
DEALLOCATE ENTRY1"

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-15 : 08:51:45

What are you actually trying to accomplish? What result do you expect and what result are you getting?
I think a bit more background is required. You'd normally want to reference the 'inserted' and/or 'deleted' pseudotables in a trigger. Also, I suspect there's a better solution to your problem than using a cursor (especially in a trigger).

Mark

SqlStar
Posting Yak Master

121 Posts

Posted - 2004-09-21 : 07:33:04

I am not clear about your expectation.... :(

:) While we stop to think, we often miss our opportunity :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-21 : 07:44:36

this trigger of yours makes no sense at all:
1. - this will get you only the value of the last row of returned data
DECLARE @PINASSIGNED VARCHAR (6)
select @PINASSIGNED = PinAssigned from A120T

2. - your syntay can't be ok:
PIN, -- there is no ',' before FROM
FROM A120T WHERE PINASSIGNED = @PINASSIGNED

3. - you do this
DECLARE ENTRY1 CURSOR FOR
SELECT PinAssigned
FROM A120T
WHERE PINASSIGNED = @PINASSIGNED
ORDER BY PINASSIGNED

and then this

SELECT AppType, ReqType, NwPin, NwUserId, PIN,
FROM A120T
WHERE PINASSIGNED = @PINASSIGNED

which has no sense at all.

i believe that all you need to do is:

INSERT INTO A120Main (AppType, ReqType, NwPin, NwUserId, PIN)
SELECT AppType, ReqType, NwPin, NwUserId, PIN,
FROM inserted

Go with the flow & have fun! Else fight the flow

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-21 : 08:17:43

quote:

i believe that all you need to do is:

INSERT INTO A120Main (AppType, ReqType, NwPin, NwUserId, PIN)
SELECT AppType, ReqType, NwPin, NwUserId, PIN,
FROM inserted

if my frequency is right, he's trying to save the changes from one table to another but only those corresponding to the pin that was changed, so if he doesn't find it in main then there won't be any updates there but an insert will probably be my best bet.

sabirpatel
Starting Member

22 Posts

Posted - 2004-09-22 : 04:07:31

This is from MSDN....

Note The use of cursors in triggers is not recommended because of the potentially negative impact on performance. Use rowset-based logic rather than cursors to design a trigger that affects multiple rows.

1. so first of all Avoid using cursors
2. there is no need to make it so complex. as jen has mentioned you can easily accomplish by a query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources