Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #167

Re: SSE 2008: Table Trigger

Cross-posted to 2 groups.

Show all headers | View raw

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!
 
>      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.

>    declare #Inserted insensitive cursor

Good boy!

This is actually better in a way:

    DECLARE cur CURSOR STATIC LOCAL 

STATIC and INSENSITIVE have - as far as I know - the same meaning, the
rows are copied to a work table in tempdb. This gives a predictable and
understandable behaviour. The default cursor is a dynamic cursor, and
it's good that avoided this trap.

The reason that STATIC is better is that this proprietary keyword
(INSENSITIVE is ANSI) permits you to specify LOCAL which means that
the cursor disappears when then scope exits, no matter how it exits.
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.

>    open #Inserted
> 
>    fetch next from #Inserted into @ACUK,@ACOrd,@ACName,@ACNr
> 
>    while @@fetch_status=0


I prefer to write this as


     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.



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

SSE 2008: Table Trigger Gene Wirchenko <genew@ocis.net> - 2011-04-12 13:46 -0700
  Re: SSE 2008: Table Trigger Erland Sommarskog <esquel@sommarskog.se> - 2011-04-13 00:09 +0200
    Re: SSE 2008: Table Trigger Gene Wirchenko <genew@ocis.net> - 2011-04-12 17:27 -0700
      Re: SSE 2008: Table Trigger Erland Sommarskog <esquel@sommarskog.se> - 2011-04-13 08:27 +0000

csiph-web