Re: generic way to get primary key values inside trigger

On Apr 5, 3:42 pm, 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

Maybe someone else will jump in with some code for your solution. There's a bunch of samples of stuff in sites like asktom.oracle.com of sql code generating sql ... 2 common ways to do it 1 is to use sqlplus and the spool command 2nd is to use utl_file.

There's a real benefit in cdos to putting together a little effort to show some of us "you are trying" with some sample code rather than asking for a complete solution.

It's a busy week and maybe next week I could hack it out ( shouldn't take long ) but why don't you show us what you could do. You do seem to have some of the right ideas so far. Received on Thu Apr 05 2007 - 14:55:51 CDT