Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    28

    Question Unanswered: Can I use substitution variables in a trigger

    Greetings,
    I want to create an audit table of changes to a record, but instead of keeping a copy of the whole record I want an audit table whose columns are 'primary key', column_name, Old_value, new_value, audit_dt, audit_by and insert the values (primary_id, 'column1', :Old.column1, :new.column1, sysdate, user). The problem is I want the trigger to stand up even if columns are removed or added so instead of hardcoding columnnames I want to be able to get a list of column names, compare the values of the :Old and :new for each columnname in the list, and if they're not equal insert a record into the audit table. I'm running into a problem with the way I'm trying to do it, and that is; how do you use the value of a variable as the column name in a statement. Here's the closest I came which should better show the issue, but with this one I of course am prompted to supply a value for the variable when compiling...

    CREATE OR REPLACE TRIGGER "ATROBI"."INSERT_REPTEST_AUDIT" BEFORE UPDATE ON "ATROBI"."REPTEST" FOR EACH ROW
    DECLARE
    CURSOR reptest_fieldnames IS
    SELECT column_name FROM dba_tab_columns
    WHERE owner = 'ATROBI' AND table_name = 'REPTEST';
    v_fieldname varchar2(30);
    columnname varchar2(12) := '&v_fieldname'
    BEGIN

    OPEN reptest_fieldnames;
    LOOP
    FETCH reptest_fieldnames INTO v_fieldname;
    EXIT WHEN reptest_fieldnames%NOTFOUND;

    IF :new.'&columnname' <> :Old.'&columnname' THEN

    INSERT INTO reptest_audit
    VALUES (:new.reporter, v_fieldname, :Old.'&columnname', :new.'&columnname', SYSDATE, user);

    END IF;

    END LOOP;
    CLOSE reptest_fieldnames;

    END;

    So how can you store the column name in a variable like columnname then use that variable when comparing the :new and :Old????? Thanks for any help. BTW I'm using Oracle 8i
    Last edited by arobinson98; 05-20-04 at 17:39.

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I do the same, but regenerate audit triggers after a structure change. Generation of audit triggers is based on tokens stored within column and table comments.

    Not ideal, but works well. See attached image.

    Hth
    Bill
    Attached Thumbnails Attached Thumbnails trg.bmp  
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, unfortunately there is simply no way to refer to :new and :old values dynamically in a trigger - the column names must be static. As Bill says, the best you can do is build a utility to re-generate the trigger code after a table change.

  4. #4
    Join Date
    Jan 2004
    Posts
    28

    Unhappy Okay, no using variables that store column names

    Can you reference columns positionally somehow so instead of saying :new.first_name you could say :new.position1? Probably not eh?

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, that would fall fould of the "no way" restriction ;-)

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Here is what I'd consider doing.
    CREATE TABLE CHANGE_AUDIT
    ( TABLE_NAME VARCHAR2(30),
    COLUMN_NAME VARCHAR2(30),
    OLD_VALUE VARCHAR2(4095),
    NEW_VALUE VARCHAR2(2095)
    )
    Then the trigger just inserts the new row as data changes are made.
    Yes, there is a size/datatype limitation, but it might be better than nothing.

    HTH & YMMV

    HAND!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    It looks like you have to create a function with input parameters - table_name and output string the columns for this table (dba_tab_columns) . Then create a DDL trigger on the tables you want to audit, check after DDL if the columns where changed and if so, generate a new CREATE OR REPLACE TRIGGER DDL statement.

    Once you have it, create the new trigger with the changed :NEW.colname :OLD.colname values. Looks like a bit of coding, but will be a good brain exersice..

    HTH,

    clio_usa - OCP 8/8i/9i DBA

  8. #8
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Would it make sense to write a code generator that can create a file with the trigger definition?

    That is probably the easiest way to do this. The advantage is that the code generator can be used for other tables, too. And can be run at regular intervals (daily, weekly, etc.) or whenever some schema changes are made.

    I tried using a dynamic PL/SQL block to run from within a trigger, but Oracle complains.

    Will try out a few other things and post here soon.

    Ravi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •