XPost: microsoft.public.sqlserver.programming   
   From: esquel@sommarskog.se   
      
   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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)