Re: generic way to get primary key values inside trigger

On Apr 5, 5:32 pm, DA Morgan <damor..._at_psoug.org> wrote:
> akhashnob..._at_hotmail.com wrote:
> > On Apr 5, 2:43 pm, "hpuxrac" <johnbhur..._at_sbcglobal.net> wrote:
> >> On Apr 5, 2:23 pm, akhashnob..._at_hotmail.com wrote:
>
> >>> I need to get the string representation of the value of the primary
> >>> key (possibly multi-column) of the current row being inserted into a
> >>> table from within a post-insert trigger.
> >>> The code should not have any hard-coded dependency on the schema of
> >>> the current table.
> >>> The following is a schema-dependent implementation of this
> >>> -- create a table
> >>> create table test(
> >>> data_type_id number(11),
> >>> name varchar(20),
> >>> primary key(data_type_id, name)
> >>> );
> >>> --attach a trigger
> >>> --this is the non-generic version
> >>> create or replace trigger test_trigger
> >>> after insert or update on test
> >>> for each row
> >>> declare
> >>> keystring varchar(100);
> >>> begin
> >>> -- I need a generic version of the following line
> >>> select '[' || :new.data_type || '][' || :new.name ']' into
> >>> keystring from dual;
> >>> end;
> >>> /
> >>> Any help is appreciated. Thanks
> >> First I would have to say "why do you want to do this in the first
> >> place" and second that any attempt to do it generically would prove to
> >> be an even bigger obstacle to developing scalable applications than
> >> post insert triggers ( which aren't a very good idea to begin with ).
>
> >> It wouldn't be hard to write some sql that creates sql to create a
> >> trigger for each table in a schema ( or database ). For each relevant
> >> table use utl_file to write to a file and as you process the table
> >> determine the key names and then go from there.
>
> > I want to append some auditing information into a separate table that
> > gets triggered by the inserts. The table that stores this audit
> > information is common for many different tables (hence a string
> > representation of the key). So I want to be able to attach
> > essentially the same boilerplate trigger code into all the monitored
> > tables and use the keystring representation as a soft-pointer back to
> > the inserted row.
>
> > Right now, I can retrieve the primary key column names from the
> > user_constraints and user_cons_columns tables, but I'm still stuck on
> > how to use this information to dynamically generate the required sql
> > to produce a single string result. (I'm new to oracle, so a code
> > snippet would be valuable)
>
> > I'm working with oracle 10g.
>
> > Thanks.
>
> That is why Oracle created Fine Grained Auditing.
> Why reinvent the wheel?
>
> www.psoug.org
> Click on Morgan's Library
> Click on DBMS_FGA
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Thanks for the response.

I've looked at oracle fga(briefly), and I don't think it can do what I want.

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). 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). Received on Thu Apr 05 2007 - 19:58:43 CDT