Hi,

I have a trigger which contains a cursor such as the example below.

DECLARE cCur CURSOR FOR
select aFld from aTable where bFld = @aVar and cFld = bVar and dFld =
@cVar

this declare intermittently fails, on one occasion the declare
succesfully executes but then it can fail.

I have checked that the data is in the table once I get an error on the
client app that fires of the trigger for the select I declare the
cursor for.

in a previous situation removing some comments in my code has solved
this issue but I do not see how that can be related. Comments were
started with: --

anyone?

regards,

Gerard

I forgot to mention that removing the where clause from the select of
the cursor does not change this behaviour and that would return
multiple rows

It's pretty much impossible to say without seeing the actual code, not
pseudo code for the trigger.

Show quoteHide quote

DECLARE dSeq CURSOR FOR select SEQNUMBR    from SOP10102 where SOPNUMBE =
@DocumentNumber and SOPTYPE = @DocumentType and DISTTYPE = 1
OPEN dSeq
FETCH NEXT FROM dSeq INTO @LSeq
WHILE @@FETCH_STATUS = 0
BEGIN

-- do some operations on other table based upon the SEQNUMBR

END

Do you have the error message you get when it fails? And also the rest of
the trigger code - is something happening prior to the declare, or is it a
locking problem (lock timeout perhaps) on the declare.

Without the full code and exact error message things get a bit hard!

Don't you just love Great Plains naming (un)conventions though!

Mike John
Show quoteHide quote

Yes as Mike pointed out we need to see the actual trigger code.

Show quoteHide quote

Thanks for the feedback, on further investigation (after Mike's hint) I
have determined it is a locking issue.. and yes I love Great Plains ...
with a vengeance that is ..

happy holidays..

Gerard

Gerard wrote:
Don't put cursors in triggers. Just don't. Updates are set based,
triggers should be too.

David,

I appreciate your feedback but is this not a deja vu?

In my case I don't have a lot of choice other then to use triggers and
cursors within these triggers, you can take my word on it.

Even though they are set based the application I'm programming against
does all of the updates important to me on a row by row basis.

So just to be sure I do a check in the beginning of the trigger to see
if there is only one row.. (and a hell of a lot of other checks for
that matter)

set @rwcnt = @@rowcount
if @rwcnt = 0 or @rwcnt > 1 return

anyways thanks and happy holidays to you too,

Gerard

On 23 Dec 2005 13:21:02 -0800, Gerard wrote:

Show quoteHide quoteHi Gerard,

If you already checked to make sure that only one row will be returned
by the cursor, then why put in in a cursor at all?

DECLARE MyCurs CURSOR
FOR SELECT Column
    FROM   something that returns exactly one row
OPEN MyCurs
FETCH NEXT FROM MyCurs INTO @Column
CLOSE MyCurs
DEALLOCATE MyCurs

is exactly equivalent to

SELECT @Column = Column
FROM   something that returns exactly one row

Best, Hugo

Show quote Hide quote Gerard,

Try posting the entirety of the code involved (see
http://www.aspfag.com/5006), including full DDL for the tables and
the trigger, and sample data.

I'll bet someone will re-write it without the cursor for you.


Sincerely,

Chris O.

Gerard wrote:
Elaborate please. Are you saying this is a management constraint ("the
boss won't let me") or a technical one?

That doesn't mean you need to use a cursor. It's still better not to.

In that case you definitely don't need a cursor.

It sounds rather factual that, how do you know? Just because I check at
the beginning of the trigger that there is only one row doesn't mean
that there can be no need for a cursor. If you had a look at my post
with the cursor code in it you should have noticed that the cursor is
not for inserted but for a regular table. And the cursor can contain
more then one row in my scenario, otherwise I wouldn't have used it.

What is bugging me a bit is that by stating that I shouldn't use a
cursor in a trigger or simply don't need one (even though there is no
reason to conclude that based on the info I provided in my post) you
sort of jump over the actual issue. Which is the fact that the cursor
intermittently fails. No I have been able to ascertain what the issue
is, see my post from Dec 23, 10:07 pm, so that's that sorted.

Your soundbites about not using cursors in triggers haven't helped much
in that though.

regards,

Gerard

If you guarantee that there is only one row, then there absolutely no need
to invoke the overhead and expense of a cursor.

If you had posted the entire trigger code, someone might have given you free
consulting work and conducted a targeted evaluation of the code in order to
determine if you **truly** need a trigger.  The only code snippet you posted
was the trigger declaration and a non-functional loop.  "do some operations
on other table ..." isn't particularly meaningful or definitive.

You gave little information - and there was insufficient information to
determine that you needed a cursor.  If you are going to use a cursor, you
need to justify its use.  Why?  Because the appropriate technique to use in
**good** tsql programming is to use a set-based approach.  Certainly there
are times when an iterative approach is needed (or required).  These are
rare and should only be chosen because a proper set-based approach is either
not possible or not "satisfactory".

Perhaps next time you will post the actual error message in its entirety.
Based on the snippet of code you did post, you might also want to
investigate appropriate error handling logic within tsql (limited as it is).

Then it would appear that you have learned little from this ordeal.  But who
knows; you never posted the actual solution that you implemented.
http://www.catb.org/~esr/faqs/smart-questions.html

Hi Scott,

First of thanks for the elaborate reply. But just as David you seem to
have missed out on something crucial here.
The cursor is >>NOT<< for the >>inserted table<< but for another table,
see below:

I wrote:
On your other remarks;

That's just it I don't think I need to justify the use of a cursor in
my trigger, or anywhere else for that matter, to anyone. That doesn't
mean to say that I don't take its expense into consideration, on the
contrary. You can also take my word on it that a cursor is required in
my code and if you don't want to do that then fine, that's not my
problem.

Thanks for the offer but no thanks, I like my consultants a bit more
flexible and openminded. And the determination of: *if I need a
trigger*, is not a question of if, it is a requirement.

Well that's a thought, this is something I, or someone else, can do
something with. It's at least it's a suggestion worth looking at. Not
soundbites like: *Don't put cursors in triggers. Just don't* and *In
that case you definitely don't need a cursor*

Well I have learned quite a lot actually, first but foremost that some
posters may have a tendency to just shout out there mantras first and
read the posts from the person asking the question later. Second that
you stick out for each other in here, which I find a good thing. And
third that you should take the advise in here for what it's worth.

all the best,

Gerard

Gerard wrote:
I hope you've also learned that posting working code to reproduce a
problem, together with DDL and a few rows of sample data helps avoid
misunderstandings and gets you faster answers. You could have provided
that in a fraction of the time it took you to reply to the parts of
this thread that you found less helpful!

Hi David,

I'm sure everyone that contributed to this mini discussion learned
something, and yes, in future, I will do my utmost to avoid
misunderstandings.

Sometimes one needs to spend a bit of time to get results, and I think
I got the result I wanted.

all the best,

Gerard

2 excerpts from BOL.
Read and weep.
"Transact-SQL cursors are extremely efficient when contained in stored
procedures and triggers."
"The use of cursors in triggers is not recommended because of the
potentially negative impact on performance."

Show quoteHide quote

Raymond

is that both 2000 and 2005?

great to see that there are no truths though. I'd lost all confidence.

regards,

Gerard

2000. I haven't touched 2005 yet.
I tend to agree with #2 and most of the SQL experts though.
Short triggers with no Cursors and no calls to Stored procedures.
In fact, the only cursors I use (3 of them) are in stored procedures that
are run once a week when no clients are using our site.
I could probably rewrite the procedures without the cursors but I'm lazy.

The reason I'm using a cursor is to do what cannot be done in 2000 yet
which is to add row numbers to freshly inserted records , Sql Server
2005 ROW_NUMBER() will solve all of that I understand.

And gathering from earlier posts no set based solution can do that more
efficiently. This post in particular is what I'm referring to:

http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/b8320443418b6c2f?hl=en&

Gerard wrote:
In that post Itzik is making some general statements about a class of
problems that require row numbering. That generalization doesn't apply
to every example. There are set-based solutions for row numbering that
outperform cursors in given situations, especially for some sizes of
problem (larger sets are usually better handled by paging - although
you still won't need a cursor for that).

So as many others have been suggesting, you may be completely mistaken
about needing to use a cursor. Of course we can still only guess
because you still haven't posted the actual problem...

Hi, Gerard

What error message do you get ? If it's "A cursor with the name 'cCur'
already exists." then I suggest you use a local cursor instead of a
global cursor (which is the default). Even if you don't get this error,
it is always a good idea to use the cursor with the lowest impact on
resources, for example:
DECLARE TheCursor CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
SELECT ...

Razvan

Hi Razvan,

thanks for the tips.

Gerard

Hi Razvan,

I had a good read through BOL on the matter and this definitly helps a
lot, I did not know about the LOCAL option and was accustomed to use
CLOSE and DEALLOCATE explicitly to avoid the cursor being created
twice.

Can you tell me if, when using a local cursor, it still is good
practice to close and deallocate the cursor explicity or leave it up to
SQL Server to do so?

regards,

Gerard

Gerard wrote:
Show quoteHide quoteIt's generally good practice to release resources as early as possible.
DEALLOCATE explicitly.

How do I pass a second parameter to user-defined aggregate (SQL 20
Custom number format
WHERE IN - Return NULL values
GetDate() in UDF
Popuate field with value from last row