XPost: microsoft.public.sqlserver.programming   
   From: genew@ocis.net   
      
   Dear SQLers:   
      
        I want to post batches of transactions.  The transactions have to   
   pass various checks.  The transaction must balance to zero.  Only   
   certain transaction type combinations will be allowed.  It must be   
   possible to modify a batch's transactions and have all of the   
   aforesaid checks done.   
      
        I see two ways to go about it.   
      
     1) Write a stored procedure for inserting a batch's transactions and   
   another for updating a batch's transactions.   
      
   Pros:   
        All associated data can be specified in the call.  (I will have a   
   transaction batch comment, and more importantly, each transaction   
   might have a cheque number (which is NOT a column in Transactions, but   
   in a different table).)   
      
   Cons:   
        Manipulating the cursor of transactions to be added may be   
   awkward in app code.   
        Direct app write access to the Transactions table will have to be   
   locked out.   
      
     2) Use triggers.   
      
   Pros:   
        The insert for the transactions will be easy-peasy.   
        There is no need to lock out access to the Transactions table to   
   app code as any changes will be caught by the triggers.   
      
   Cons:   
        The comment for the batch will have to be added after the batch.   
   e.g.   
             insert into Transactions ... -- all of the transactions   
             select @BatchNr= batch number jsut assigned   
             update TranBatches set Commment=@Comment where TBUK=@BatchNr   
        It still may be necessary to have cursor handling code.   
      
      
        Can I build up a cursor with stored procedures?  I am thinking of   
   a calling sequence like:   
             CreateTransactionBatchCursor()   
             InsertTransaction() (into the transaction batch cursor)   
   repeated as many times as needed   
             PostTransactionBatch() which would clear the cursor if   
   posting occurred.   
      
      
        Updating I think I can handle by building a cursor by doing in   
   order   
             Get existing transactions under of the specified batch   
   number.   
             Delete by PK of everything in Deleted.   
             Insert of everything in Inserted.   
      
      
        I am leaning to stored procedures.  Am I missing any important   
   considerations?  Which way would you go?   
      
   Sincerely,   
      
   Gene Wirchenko   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)