On Apr 6, 2:56 pm, "EdStevens" <quetico_..._at_yahoo.com> wrote:
> 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)
> )
> /
> create or replace trigger mytrigger
> before insert or update on mytable
> for each row
> begin
> if inserting then
> select sysdate into :new.aud_create_date from dual;
> select user into :new.aud_create_user from dual;
> else
> select sysdate into :new.aud_mod_date from dual;
> select user into :new.aud_mod_user from dual;
> end if;
> end;
> /
This solution would overwrite previous entries. I want my audit
information to be purely additive. Also, I want to to try and
maintain a clean separation between audit and functional aspects
within the schema.
Received on Fri Apr 06 2007 - 14:21:33 CDT
