Trigger :old and :new column values

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.
Received on Wed Nov 24 1999 - 06:07:24 CST