Re: Trigger :old and :new column values

...But the cursor I was using was to find out which 2 or 3 of the 40 or 50 columns values I actually want to audit

In my "Audit" table, I want to have one entry for each column I'm interested in, not one record for the change....It will be a generic audit table for any column on any table.....

Ideally, you see, I want some GENERIC auditing trigger code so if I decide I'm interested in a particular column on a particular table, I just put an entry in the INTERESTS table for it and it starts getting audited - I don't need to add more trigger code.....

I've spoken to Oracle Support since I posted the original message and they've said that it CATEGORICALLY cannot be done....No work arounds, nothing. There is no way of referencing :old and :new columns any other way than explicitly naming the columns you want !

Dave

In article <383bd0e7.127010443_at_cnews.newsguy.com>,   (SightBlinder) wrote:
> You don't need to use a cursor within a trigger. Triggers have their
> own functionality for looping thru records that are changed in the
> table that has the trigger on it. You can run the trigger on the
> statement level or on the row level. From your code it looks like you
> want to audit any change to the base table. Try something like:
>
> CREATE OR REPLACE TRIGGER basetabletrigger
> BEFORE
> UPDATE OR DELETE OR INSERT ON basetable
> FOR EACH ROW
> BEGIN
> IF UPDATING THEN
> INSERT INTO audittable VALUES
> (..., :new.column1, :old.colum1, ...);
> ELSEIF DELETING THEN
> delete audit code
> ELSIF INSERTING THEN
> insert audit code
>
> The options and decisions of how to use a trigger are very robust.
> You will probably need to look at the documentation to get a feel for
> what you can do.
> hth ~Jim
>
> On Wed, 24 Nov 1999 10:17:09 GMT, Imagecodedata_at_netscape.net wrote:
>
> >Please bear with me, my question needs a bit of explanation !
> >
> >I'm trying to write some "auditing" code in a TRIGGER which saves old
> >and new values of particular columns on particular tables I'm
> >interested in.......
> >
> >
> >I've got some code like this.....
> >
> > CURSOR c1(pType VARCHAR2
> > )
> > IS
> > SELECT COLUMNNAME FROM INTERESTS
> > WHERE INTERESTS.TYPE = pType;
> >
> >which is followed a bit further on by....
> >
> > FOR changes_rec IN c1(sType) LOOP
> >
> > INSERT INTO AUDIT_CHANGES
> > VALUES(
> > AUDIT_CHANGES_SEQ.NEXTVAL,
> > sType,
> > changes_rec.COLUMNNAME,
> > *:old.ColumnName*, <--- Problem is here !
> > *:new.ColumnName*, <---
> > );
> > END LOOP;
> >
> >For each columnname returned in the c1 "INTERESTS" table query I want
> >to save the OLD and NEW values in my AUDIT_CHANGES table,
> >
> >How do I reference the column in the :old and :new records when I've
> >only got the column's name in <changes_rec.ColumnName> ?!
> >I figure I need some way of "Evaluating"
:old.(changes_rec.ColumnName)
> >but I can't find one....
> >
> >Any suggestions ??
> >
> >Dave
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 26 1999 - 02:44:13 CST