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

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
Unfortunately, this is a bit too vague to be able to say what might be
the best. All I can say is that from the business domain I come from,
updating transactions is a big no-no. You can cancel them, but you
cannot modify them.

But I suspect that this is just a made-up exercise you make for your
training. So why not do both solutions, and see which one you like the
best?



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

On Fri, 06 May 2011 23:46:56 +0200, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Not made up. I may well use it. I do not need auditing
capability.

Because if I did that every time I ran into an issue, I still
would not know what is the better *general* solution. I want to know
that. If a particular application is written less efficiently, that
is acceptable to me as long as I have a generally good method. (I can
fine-tune later.)

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
There may not be any best "general" solution. And if there is, it may not
be the best for you.

My main language besides SQL is Perl, and in Perl the motto is "There is
more than one way do it!". I have found that this applies very much to
SQL as well.

As for the question to use stored procedures or triggers, this is not always
an easy question. At the same time, triggers and procedures serves different
purposes.

In the bottom of it all, there is a table. A table describes and entity
and has a number of attribuets. To the table comes a number of rules. Some
are very simple, like the customer id of an order cannot be NULL. Others
are far more complex. Some rules can be expressed with constraints,
and this is generally the preferred way.

But not all rules are easily expressed with a constraint. For instance,
you can require that the customer of an order must exist with help of a
foreign-key constraint. But the requirement that the customer of a new
order must be an active customer, is not equally easily expressed with a
constraint. To this end we with have triggers. Triggers are general
features, and you can do about anything in a trigger. But there are also
several disadvantages compared to constraints:
1) They are more complex to write and not the least they require more
routine code to raise errors etc.
2) A trigger can more easily disappear than a constraint.

You could say that a trigger is further away from the table than a
constraint. Nevertheless, the most common view is that the trigger
is part of the table and holds up the rules for the table.

A stored procedure on the other hand is an autonomous module and is
not tied to a table. Simply put - it is application code in the database.
But if you think of, this is also true for a trigger. The trigger is
tied to a table, but if it operates on other tables, it's an application
with regards to those tables.

Many advocate putting complex cascading updates in stored procedures
rather than in triggers. And there is certainly a merit in this. If
you identify a rule which says that if a number of items are added to
and order, the inventory should be reduced, it makes sense to have this
logic in one place - in a stored procedure. Not the least if you later
in the procedure need to operate to the reduced inventory. If you rely
on a trigger to do the work you have to split your logic in two disjunct
places.

There is also the problem that if trigger is disabled to dropped, you
will not notice, which means that the integrity of the database is
silently violated. Very bad. If someone drops a stored procedure,
this results in an error, and the database is protected.

But the drawback with stored procedure is that if someone bypasses the
stored procedure and updates the table directly for maintenance reasons
or whatever, there is nothing to enforce the rules. The person doing
the update must be aware of that he manually has to reduce the inventory.

By means, this is not a simple question. Over the years, I have come to
more and more favour triggers over stored procedure for actions. Not
the least because in the environment I work, there are updates directly
against the tables by people who don't know as much as they should.
(Not end users, but support people, often when the application puts up
a roadblock for the suers.)

But doing everthing in triggers also means that code is more difficult
to maintain and understand. Stored procedures may also be nested, but
if one procedure calls another you see that in the code. But if a table
has a trigger that makes a lot of changes, this is nothing which stands
out in the same way.

So this is largely a non-answer to your question. But it is my hope that
you will be confused on a higher level.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

Erland Sommarskog schreef op 07-05-2011 15:09:

It has to be said, when you annoy us, at least you do it with a sense of
humour. If one has to be tortured, it might as well be done with a grin.