Re: generic way to get primary key values inside trigger

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 05 Apr 2007 14:32:12 -0700
Message-ID: <1175808729.293093@bubbleator.drizzle.com>

akhashnobish_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
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Apr 05 2007 - 16:32:12 CDT