At 07:27 PM 31/10/2005 +0000, you wrote:

>FB 1.5
>
>I'm looking for some kind of general technique to prevent changes to
>invoice records, except under certain conditions.
>
>The general table setup is like this (abreviated for clarity):
>
>Orders - fields INVOICENUM (PK), ORDER_DATE (not null), INVOICE_DATE
>(can be null before being invoiced), TOTAL
>
>OrderItems - fields IDNUM (PK), INVOICENUM, ITEMNUM, QTY_ORDER,
>QTY_SHIP, PRICE
>
>When an order is invoiced, the ORDERS.INVOICE_DATE is filled in and
>the ORDERITEMS.QTY_SHIP * ORDERITEMS.PRICE is the value for
>ORDERS.TOTAL.
>
>Once the ORDERS.INVOICE_DATE is not null (i.e. Invoiced) I do not
>want to allow changes except by the SYSDBA.
>
>I know I can intercept any changes via a trigger in ORDERITEMS and
>ORDERS, however in the case of ORDERITEMS I would have to check with
>a select statement like this inside the trigger:
>
>Select INVOICENUM from ORDERS where INVOICENUM = old.INVOICENUM and
>INVOICE_DATE is not null
>
>This seems like an expensive operation to call for each item in the
>order since there might be several hundred items in an order and
>several clients filling orders at the same time.

Why does that make testing the ORDERS table an expensive operation? It is
one read of ORDERS for each attempt.

>I'm sure this is something that others have faced something like
>this. Is there a better way to prevent changes to an invoiced order?

Create an exception:

create exception CANNOT_UPDATE_ORDER 'Order is already invoiced' ;
commit;

Do the trigger like this:

create trigger bIbu_orderitems for orderitems
active before update or insert as
begin
if (CURRENT_USER <> 'SYSDBA'
AND EXISTS (
SELECT 1 FROM ORDERS
WHERE INVOICENUM = NEW.INVOICENUM
AND INVOICEDATE IS NOT NULL) ) then
exception CANNOT_UPDATE_ORDER;
end

This eats very little and might also benefit from having an ascending index
on orders.invoicenum, since you would usually be querying recent orders.

One problem with this approach is that, in concurrency isolation, this
transaction won't avoid any orders that another transaction has invoiced
since this transaction began. Presumably you are taking care of this
eventuality with your invoicing process...possibly with some form of
pessimistic locking on the tables.

./heLen