Re: generic way to get primary key values inside trigger

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. Received on Thu Apr 05 2007 - 14:42:50 CDT