0



I am trying to develop a trigger to capture the details on update on table - TEST_TABLE. I have created an audit table to capture the details, but need some way to pick up ONLY the modified column and old/new values. Have written a procedure CHECK_VAL to check for difference, and a cursor to iterate through all the column names, concatenated by the ":NEW" and ":OLD" pseudo columns. 

create or replace PROCEDURE CHECK_VAL( 
    L_NEW IN VARCHAR2,
    L_OLD IN VARCHAR2,
    LC_IS_DIFF out varchar2)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('IN PROC CHECK_VAL : NEW_VAL '||L_NEW||' OLD: '||L_OLD);

    IF ( L_NEW <> L_OLD OR
        (L_NEW IS NULL AND L_OLD IS NOT NULL) OR
        (L_NEW IS NOT NULL AND L_OLD IS NULL) )
    THEN
         DBMS_OUTPUT.PUT_LINE('IN PROC CHECK_VAL IF');
         LC_IS_DIFF := 'YES';
    ELSE 
         lc_is_diff := 'NO';
    END IF;
END;


/*************/

 create or replace trigger xxtest
    before update on test_table
    for each row
    declare
        ln_cnt number := 0;
        lc_new varchar2(50);
        lc_old varchar2(50);

        lc_col varchar2(50);
        lc_stat varchar2(50);
        cursor lcu_c1 (p_tbl_name varchar2)
        is 
            select column_name
            from user_tab_columns
            where table_name = p_tbl_name;
        lc_column lcu_c1%rowtype;
    begin
        for lc_column in lcu_c1('TEST_TABLE')
        loop
            dbms_output.put_line('In loop ');
            lc_col := lc_column.column_name;
            lc_new := ':NEW.' || lc_col;
            lc_old := ':OLD.' || lc_col;
            check_val(lc_new,lc_old,lc_stat);
            dbms_output.put_line('Column '||lc_col);
            dbms_output.put_line('Changed? '||lc_stat);
            if lc_stat = 'YES' then
                insert into audit_tbl values (sysdate,lc_col);
            end if;
        end loop;
        dbms_output.put_line('Exit trigger');
    end;


/**********/

update test_table
set col2= 'DX'
where col1= 'A';



After running update on the table - TEST_TABLE, the output is as follows. There is an issue because the o/p for ':NEW.||lc_col1' is basically a string with ':NEW.column1' INSTEAD of the value of :NEW.col1.
Any help would be appreciated.

Thank you.!

Output:-

"
1 row(s) updated.
In loop 
IN PROC CHECK_VAL : NEW_VAL :NEW.COL1 OLD: :OLD.COL1
IN PROC CHECK_VAL IF
Column COL1
Changed? YES
In loop 
IN PROC CHECK_VAL : NEW_VAL :NEW.COL2 OLD: :OLD.COL2
IN PROC CHECK_VAL IF
Column COL2
Changed? YES
In loop 
IN PROC CHECK_VAL : NEW_VAL :NEW.COL3 OLD: :OLD.COL3
IN PROC CHECK_VAL IF
Column COL3
Changed? YES
Exit trigger
"
Aftab
  • 83
  • 2
  • 6
  • Will the `UPDATING` function help you do what you want? You can include `IF UPDATING('MYCOL1') THEN...` in your trigger. I don't believe this tests if the column was updated to something different, just that it was included in the column list for update. – Tad Harrison Mar 04 '20 at 15:38
  • @TadHarrison no, `UPDATING` only tells you if the `update` statement included the column in its update - it is true even if the actual data wasn't actually changed. – Jeffrey Kemp Mar 19 '20 at 07:26

1 Answers1

0

You are passing the strings ':NEW.columnname' and ':OLD.columnname' to your check_val procedure. Then you are testing whether these strings are different which, of course, they always are (one has 'NEW' as chars 2,3 and 4 and the other has 'OLD').

What you should be passing is the values in the :NEW.columnname and :OLD.column_name variables.

Niall
  • 66
  • 4