Re: generic way to get primary key values inside trigger

Comments embedded.
On Apr 5, 7:58 pm, amit.khashnob..._at_gmail.com wrote: [snipped]
>
> I need to be able to (quickly) go from a given row in the monitored
> table to related audit information for that specific row (eg, who
> inserted this).

Why, then, are you designing this to use a generic 'catch-all' table for this auditing information? That appears, to me, to be quite inefficient as you'll need to plan for the largest of primary key values for this to work. Why not set up individual audit tables for the source tables you desire to monitor? Add to the existing column list for each monitored table a USERNAME column, a PROCESS_DT column and an OPERATION column, populating those, and the rest of the data, with your trigger. I've used this technique with various clients, including commercial airline maintenance services, and it works very well. You won't be able to generate 'generic' code to access the :NEW values since the NEW block is table-dependent and only lasts within the context of the transaction (commit and rollback erase this information).

> With fga, it seems to me, you can store the user
> query and the audit condition for the entire table, but those in
> themselves are insufficient for this purpose(without expensive
> processing).- Hide quoted text -
>
> - Show quoted text -

Then write your own 'FGA'-type auditing as I've outlined above. I've used such configurations to track entire part histories from creation to deletion in a system without issue. And the FAA (which is the Federal Aviation Administration, for those not familiar with the agency) was happy with the results.

David Fitzjarrell Received on Fri Apr 06 2007 - 09:58:11 CDT