XPost: microsoft.public.sqlserver.programming
From: genew@ocis.net
On Wed, 13 Apr 2011 00:09:39 +0200, Erland Sommarskog
wrote:
>Gene Wirchenko (genew@ocis.net) writes:
>> I prefer to have the one trigger instead of two nearly-identical
>> triggers. That is why I have @AccessType. Good or bad? Is there a
>> better way of determining between insert and update?
>
>You are on the right track, but this is better:
> IF exists (SELECT * FROM deleted)
>No need to actually count the rows!
Lazy is good. Thank you.
>> What sort of error handling should I have? While this code does
>> work for multiple rows, I need a handling for if one or more
>> inserts/updates fail, and it should be something that returns
>> reasonable detail to the application.
>
>By default when you are in a trigger, the first error will abort the batch
>and rollback the transaction.
That will do for now.
[snip]
>The default is global which means that the cursor still exists. Which
>can cause nasty surprises next time.
>
>In fact, I would expect this to happen to you, because you only have
>CLOSE at the end. You need DEALLOCATE instead. With CLOSE the cursor
>still exists.
>
># in the beginning of the cursor name is a little odd.
I was thinking it would then be temporary. Does that not work
with cursors?
[snip]
> WHILE 1 = 1
> BEGIN
> FETCH cur ...
> IF @@fetch_status <> 0
> BREAK
>
>Then you only need FETCH statement, and one less line to change if you add
>one more column to the cursor.
Ugly, but yes on maintainability.
Sincerely,
Gene Wirchenko
--- SoupGate-Win32 v1.05
* Origin: you cannot sedate... all the things you hate (1:229/2)
|
