--- In firebird-support@yahoogroups.com, "Rick Roen" <rick@s...> 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.
>

If I am not mistaken, it is a simple select on a primary key which
would be quite fast. It is going to return 0 or 1 record and will have
an index with perfect selectivity to use. You should probably use
CURRENT_ROLE instead of checking for SYSDBA. It is generally
discouraged to use SYSDBA as a regular user, and you can use roles to
do what you want.

Alternatively, you could do something like this

update orders set editing='T' where invoicenum=123454;

(Create a trigger to raise an exception if appropriate rights are not
had).

Now you can make any changes you like to the orderitems table for that
invoicenum.

update orders set editing='F' where invoicenum=123454;

if you always follow that procedure, then you will not need to check
it for every record.


Adam