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

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

SSE 2008: Handling Transaction Batches

Cross-posted to 2 groups.

Show all headers | View raw

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

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

Thread

SSE 2008: Handling Transaction Batches Gene Wirchenko <genew@ocis.net> - 2011-05-05 16:25 -0700
  Re: SSE 2008: Handling Transaction Batches Erland Sommarskog <esquel@sommarskog.se> - 2011-05-06 23:46 +0200
    Re: SSE 2008: Handling Transaction Batches Gene Wirchenko <genew@ocis.net> - 2011-05-06 15:06 -0700
      Re: SSE 2008: Handling Transaction Batches Erland Sommarskog <esquel@sommarskog.se> - 2011-05-07 15:09 +0200
        Re: SSE 2008: Handling Transaction Batches Henk van den Berg <hvandenberg@xs4all.nl> - 2011-05-07 18:31 +0200

csiph-web