Re: generic way to get primary key values inside trigger

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