| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: generic way to get primary key values inside trigger
On Apr 5, 7:58 pm, amit.khashnob..._at_gmail.com wrote:
> 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).
If that's all you want, why not just add some columns to the table in question and populate them with an 'on insert' trigger:
create table mytable
(ssn varchar2(9),
lname varchar2(20),
fname varchar2(20),
aud_create_date date, aud_create_user varchar2(20), aud_mod_date date, aud_mod_user varchar(20)
 |
 |