XPost: microsoft.public.sqlserver.programming   
   From: genew@ocis.net   
      
   Dear SQLers:   
      
        I have a basic trigger that does most of what I want.  I am   
   wondering if it is good design.   
      
        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?   
      
        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.   
      
   ***** Start of Code *****   
   create trigger trgIUAccounts on Accounts   
   instead of insert, update   
   as   
      begin   
      declare @AccessType int     -- 0: row insert, 1: row update   
      if (select count(*) from Deleted)>0   
         select @AccessType=1   
      else   
         select @AccessType=0   
      
      -- NiceString() Parameters   
      declare @RetVal int   
      declare @strRaw nvarchar(max)   
      declare @strNice nvarchar(max)   
      
      declare #Inserted insensitive cursor   
      for select ACUK,ACOrd,ACName,ACNr from Inserted   
      
      -- Current Row's Column Values   
      declare @ACUK nvarchar(4)   
      declare @ACOrd int   
      declare @ACName nvarchar(40)   
      declare @ACNr nvarchar(20)   
      
      open #Inserted   
      
      fetch next from #Inserted into @ACUK,@ACOrd,@ACName,@ACNr   
      
      while @@fetch_status=0   
         begin   
         select @strRaw=@ACName   
         execute @RetVal=NiceString @strRaw,@strNice output,0,1,1   
         if @RetVal>0 and @strRaw<>@strNice   
            select @ACName=@strNice   
   --      else   
   --         begin   
   --         --***** error   
   --         end   
         --*****TODO: Do the same for ACNr.   
         if @AccessType=0   
            insert into Accounts   
             (ACUK,ACOrd,ACName,ACNr)   
            values   
             (@ACUK,@ACOrd,@ACName,@ACNr)   
         else   
            update Accounts   
            set ACOrd=@ACOrd,ACName=@ACName,ACNr=@ACNr   
            where ACUK=@ACUK   
      
         fetch next from #Inserted into @ACUK,@ACOrd,@ACName,@ACNr   
         end   
      
      close #Inserted   
      end   
   ***** End of Code *****   
      
   Sincerely,   
      
   Gene Wirchenko   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)