13 Replies Latest reply: Nov 23, 2012 4:11 AM by BluShadow RSS

    Dynamic references to trigger vars :new and :old

    15060
      Is there any way to dynamicly refer to the :old and :new bind
      variables available in triggers?

      I'm looking for a method to make a standard processing system
      that processes based on column-names. I want to avoid calling my
      handling system staticly for each field in the table.

      Ie. I want to refer to :old.data_column in a way where I can
      loop through all the fields of the table.

      Hints, tips and ideas would be greatly apprechiated.
        • 1. re:Dynamic references to trigger vars :new and :old
          24837
          Hi,
          Your problem is not clear..can you explain with some sample
          code..
          Regards,
          G. Rajakumar.
          • 2. re:Dynamic references to trigger vars :new and :old
            Zlatko Sirotic
            You can't have dynamic PL/SQL containing ":NEW" or ":OLD" in
            database triggers.

            But in some cases, if your table has primary key (PK)
            or unique key (UK), you can use AFTER STATEMENT triggers
            in following way
            (this is like well-known solution for mutating error problem):

            1. create database package with PL/SQL table
            (for storing PK values)
            2. clear PL/SQL table in BEFORE STATEMENT trigger
            3. populate PL/SQL table with PK value
            in BEFORE (or AFTER) ROW trigger
            4. read rows in AFTER STATEMENT trigger with dynamic PL/SQL,
            using PKs from PL/SQL table and using dynamically selected
            column names with statement
            SELECT column_name
            FROM user_tab_columns
            WHERE UPPER (table_name) = UPPER (p_table);"
            (you can read NEW values of columns and,
            with AUTONOMOUS_TRANSACTION, OLD values too)

            For example (scott.dept table):

            CREATE OR REPLACE TRIGGER bus_dept
            BEFORE UPDATE ON dept
            BEGIN
            plsql_table.clear;
            END;
            /

            CREATE OR REPLACE TRIGGER bur_dept
            BEFORE UPDATE ON dept
            FOR EACH ROW
            BEGIN
            plsql_table.populate_with_id (:NEW.deptno);
            END;
            /

            CREATE OR REPLACE TRIGGER aus_dept
            AFTER UPDATE ON dept
            DECLARE
            v_current_id dept.deptno%TYPE;
            BEGIN
            dynamic_new_old.set_table_name ('dept');
            dynamic_new_old.set_pk_name ('deptno');
            dynamic_new_old.create_column_names;

            WHILE plsql_table.id_exists LOOP
            v_current_id := plsql_table.current_id;

            DBMS_OUTPUT.PUT_LINE ('OLD VALUES:');
            dynamic_new_old.display_old_values (v_current_id);

            DBMS_OUTPUT.PUT_LINE ('NEW VALUES:');
            dynamic_new_old.display_new_values (v_current_id);

            DBMS_OUTPUT.PUT_LINE ('*****');
            END LOOP;
            END;
            /

            CREATE OR REPLACE PACKAGE plsql_table IS
            PROCEDURE clear;
            PROCEDURE populate_with_id (p_id dept.deptno%TYPE);
            FUNCTION id_exists RETURN BOOLEAN;
            FUNCTION current_id RETURN dept.deptno%TYPE;
            END;
            /

            CREATE OR REPLACE PACKAGE BODY plsql_table IS

            TYPE type_plsql_table IS TABLE OF dept.deptno%TYPE INDEX BY
            BINARY_INTEGER;
            m_plsql_table type_plsql_table;
            -- prefiks m_ is for module level variable (defined in package
            body)
            -- prefiks g_ is for global variable (defined in package
            specification)
            m_rec_number BINARY_INTEGER;

            PROCEDURE clear IS
            BEGIN
            m_rec_number := 0;
            END;

            PROCEDURE populate_with_id (p_id dept.deptno%TYPE) IS
            BEGIN
            m_rec_number := m_rec_number + 1;
            m_plsql_table (m_rec_number) := p_id;
            END;

            FUNCTION id_exists RETURN BOOLEAN IS
            BEGIN
            RETURN (m_rec_number > 0);
            END;

            FUNCTION current_id RETURN dept.deptno%TYPE IS
            v_id dept.deptno%TYPE;
            BEGIN
            v_id := m_plsql_table (m_rec_number);
            m_rec_number := m_rec_number - 1;
            RETURN v_id;
            END;

            END;
            /


            CREATE OR REPLACE PACKAGE dynamic_new_old IS
            PROCEDURE set_table_name (p_table VARCHAR2);
            PROCEDURE set_pk_name (p_pk VARCHAR2);
            PROCEDURE create_column_names;
            PROCEDURE display_old_values (p_id dept.deptno%TYPE);
            PROCEDURE display_new_values (p_id dept.deptno%TYPE);
            END;
            /

            CREATE OR REPLACE PACKAGE BODY dynamic_new_old IS

            m_table VARCHAR2 (30);
            m_pk VARCHAR2 (30);
            m_columns VARCHAR2 (1000);

            PROCEDURE set_table_name (p_table VARCHAR2) IS
            BEGIN
            m_table := p_table;
            END;

            PROCEDURE set_pk_name (p_pk VARCHAR2) IS
            BEGIN
            m_pk := p_pk;
            END;

            PROCEDURE create_column_names IS
            v_first_column BOOLEAN;
            BEGIN
            v_first_column := TRUE;

            FOR rec IN
            (SELECT column_name
            FROM user_tab_columns
            WHERE UPPER (table_name) = UPPER (m_table))
            LOOP
            IF v_first_column THEN
            v_first_column := FALSE;
            m_columns := 'v_record.' || rec.column_name;
            ELSE
            m_columns := m_columns ||
            '||' || '''--''' || '|| v_record.' || rec.column_name;
            END IF;
            END LOOP;
            END;

            PROCEDURE display_values (p_id dept.deptno%TYPE) IS
            v_cursor INTEGER;
            v_rows_processed INTEGER;
            v_statement VARCHAR2 (32000);
            BEGIN
            v_statement :=
            ' DECLARE ' ||
            ' v_record ' || m_table || '%ROWTYPE;' ||
            ' BEGIN' ||
            ' SELECT * INTO v_record' ||
            ' FROM ' || m_table ||
            ' WHERE ' || m_pk || ' = ' || p_id || ';' ||
            ' DBMS_OUTPUT.PUT_LINE (' || m_columns || ');' ||
            ' END;';

            v_cursor := DBMS_SQL.OPEN_CURSOR;
            DBMS_SQL.PARSE (v_cursor, v_statement, DBMS_SQL.V7);
            v_rows_processed := DBMS_SQL.EXECUTE (v_cursor);
            DBMS_SQL.CLOSE_CURSOR (v_cursor);
            EXCEPTION
            WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE (sqlerrm);
            IF DBMS_SQL.IS_OPEN (v_cursor) THEN
            DBMS_SQL.CLOSE_CURSOR (v_cursor);
            END IF;
            END;

            PROCEDURE display_old_values (p_id dept.deptno%TYPE) IS
            PRAGMA AUTONOMOUS_TRANSACTION;
            BEGIN
            display_values (p_id);
            END;

            PROCEDURE display_new_values (p_id dept.deptno%TYPE) IS
            BEGIN
            display_values (p_id);
            END;

            END;
            /


            Note that this code is not generic, because uses
            "dept.deptno%TYPE". If all your PKs has the same declaration
            (for example NUMBER), then you can write generic solution.

            If you need only OLD values, you can write a simpler solution
            (without statement triggers and "plsql_table" package),
            using "dynamic_new_old" package and AUTONOMOUS_TRANSACTION
            in BEFORE (or AFTER) ROW trigger.


            Regards

            Zlatko Sirotic

            • 3. re:Dynamic references to trigger vars :new and :old
              Zlatko Sirotic
              Instead of PK/UK, we can use ROWID. Then code is generic:

              CREATE OR REPLACE TRIGGER bus_dept
              BEFORE UPDATE ON dept
              BEGIN
              plsql_table.clear;
              END;
              /

              CREATE OR REPLACE TRIGGER bur_dept
              BEFORE UPDATE ON dept
              FOR EACH ROW
              BEGIN
              plsql_table.populate_with_rowid (:OLD.ROWID);
              END;
              /

              CREATE OR REPLACE TRIGGER aus_dept
              AFTER UPDATE ON dept
              DECLARE
              v_current_rowid ROWID;
              BEGIN
              dynamic_new_old.set_table_name ('dept');
              dynamic_new_old.create_column_names;
              WHILE plsql_table.rowid_exists LOOP
              v_current_rowid := plsql_table.current_rowid;
              DBMS_OUTPUT.PUT_LINE ('OLD VALUES:');
              dynamic_new_old.display_old_values (v_current_rowid);
              DBMS_OUTPUT.PUT_LINE ('NEW VALUES:');
              dynamic_new_old.display_new_values (v_current_rowid);
              DBMS_OUTPUT.PUT_LINE ('*****');
              END LOOP;
              END;
              /

              CREATE OR REPLACE PACKAGE plsql_table IS
              PROCEDURE clear;
              PROCEDURE populate_with_rowid (p_rowid ROWID);
              FUNCTION rowid_exists RETURN BOOLEAN;
              FUNCTION current_rowid RETURN ROWID;
              END;
              /


              CREATE OR REPLACE PACKAGE BODY plsql_table IS

              TYPE type_plsql_table IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
              m_plsql_table type_plsql_table;
              -- prefiks m_ is for module level variable (defined in package
              body)
              -- prefiks g_ is for global variable (defined in package
              specification)
              m_rec_number BINARY_INTEGER;

              PROCEDURE clear IS
              BEGIN
              m_rec_number := 0;
              END;

              PROCEDURE populate_with_rowid (p_rowid ROWID) IS
              BEGIN
              m_rec_number := m_rec_number + 1;
              m_plsql_table (m_rec_number) := p_rowid;
              END;

              FUNCTION rowid_exists RETURN BOOLEAN IS
              BEGIN
              RETURN (m_rec_number > 0);
              END;

              FUNCTION current_rowid RETURN ROWID IS
              v_rowid VARCHAR2 (18);
              BEGIN
              v_rowid := m_plsql_table (m_rec_number);
              m_rec_number := m_rec_number - 1;
              RETURN v_rowid;
              END;

              END;
              /


              CREATE OR REPLACE PACKAGE dynamic_new_old IS
              PROCEDURE set_table_name (p_table VARCHAR2);
              PROCEDURE create_column_names;
              PROCEDURE display_old_values (p_rowid ROWID);
              PROCEDURE display_new_values (p_rowid ROWID);
              END;
              /

              CREATE OR REPLACE PACKAGE BODY dynamic_new_old IS

              m_table VARCHAR2 (30);
              m_columns VARCHAR2 (32000);

              PROCEDURE set_table_name (p_table VARCHAR2) IS
              BEGIN
              m_table := UPPER (p_table);
              END;

              PROCEDURE create_column_names IS
              v_first_column BOOLEAN;
              BEGIN
              v_first_column := TRUE;
              FOR rec IN
              (SELECT column_name
              FROM user_tab_columns
              WHERE table_name = m_table)
              LOOP
              IF v_first_column THEN
              v_first_column := FALSE;
              m_columns := 'v_record.' || rec.column_name;
              ELSE
              m_columns := m_columns ||
              '||' || '''--''' || '|| v_record.' || rec.column_name;
              END IF;
              END LOOP;
              END;

              PROCEDURE display_values (p_rowid ROWID) IS
              v_cursor INTEGER;
              v_rows_processed INTEGER;
              v_statement VARCHAR2 (32000);
              BEGIN
              v_statement :=
              ' DECLARE ' ||
              ' v_record ' || m_table || '%ROWTYPE;' ||
              ' BEGIN' ||
              ' SELECT * INTO v_record' ||
              ' FROM ' || m_table ||
              ' WHERE ROWID = ''' || p_rowid || ''';' ||
              ' DBMS_OUTPUT.PUT_LINE (' || m_columns || ');' ||
              ' END;';
              v_cursor := DBMS_SQL.OPEN_CURSOR;
              DBMS_SQL.PARSE (v_cursor, v_statement, DBMS_SQL.V7);
              v_rows_processed := DBMS_SQL.EXECUTE (v_cursor);
              DBMS_SQL.CLOSE_CURSOR (v_cursor);
              EXCEPTION
              WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE (sqlerrm);
              IF DBMS_SQL.IS_OPEN (v_cursor) THEN
              DBMS_SQL.CLOSE_CURSOR (v_cursor);
              END IF;
              END;

              PROCEDURE display_old_values (p_rowid ROWID) IS
              PRAGMA AUTONOMOUS_TRANSACTION;
              BEGIN
              display_values (p_rowid);
              END;

              PROCEDURE display_new_values (p_rowid ROWID) IS
              BEGIN
              display_values (p_rowid);
              END;

              END;
              /


              Regards

              Zlatko Sirotic

              • 4. re:Dynamic references to trigger vars :new and :old
                18338
                WOW!!!! This is genious. I have searched for a solution like this for hours. Great work.


                Henrik
                • 5. re:Dynamic references to trigger vars :new and :old
                  Zlatko Sirotic
                  But, note that because an autonomous transaction does not see any changes made
                  by the main transaction, this method does not work very well if we have 2 (or more)
                  updates on the same row in the same transaction.

                  For more details, se:
                  www.dulcian.com
                  -> FAQs -> SQL & PL/SQL -> 6. Using :OLD and :NEW values in dynamic SQL in database triggers


                  Regards,
                  Zlatko Sirotic
                  • 6. re:Dynamic references to trigger vars :new and :old
                    18338
                    Many thanks,

                    I got a NEW VERSION of this code.
                    So every change will be written to a history table. If there is a rollback, all the changes will be rolled back in the history table, too!
                    And you are able to select which cols you want to save.

                    If you have some questions to it, feel free to ask.

                    henrik.ruenger@web.de

                    P.S.: I don't know why my email field is blank. In the preferences I am not able to change my email Adress. :-(

                    Take a look at it:


                    CREATE OR REPLACE PACKAGE pg_plsql_table
                    IS
                    PROCEDURE clear;
                    PROCEDURE populate_with_rowid (p_rowid ROWID);
                    FUNCTION rowid_exists RETURN BOOLEAN;
                    FUNCTION current_rowid RETURN ROWID;
                    END;
                    /


                    CREATE OR REPLACE PACKAGE BODY pg_plsql_table IS
                    TYPE type_plsql_table IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
                    m_plsql_table type_plsql_table;
                    m_rec_number BINARY_INTEGER;
                    --
                    PROCEDURE clear IS
                    BEGIN
                    m_rec_number := 0;
                    END;
                    --
                    PROCEDURE populate_with_rowid (p_rowid ROWID) IS
                    BEGIN
                    m_rec_number := nvl(m_rec_number,0) + 1;
                    m_plsql_table (m_rec_number) := p_rowid;
                    END;
                    --
                    FUNCTION rowid_exists RETURN BOOLEAN IS
                    BEGIN
                    RETURN (m_rec_number > 0);
                    END;
                    --
                    FUNCTION current_rowid RETURN ROWID IS
                    v_rowid VARCHAR2 (18);
                    BEGIN
                    v_rowid := m_plsql_table (m_rec_number);
                    m_rec_number := m_rec_number - 1;
                    RETURN v_rowid;
                    END;
                    END;
                    /

                    CREATE OR REPLACE PACKAGE pg_dynamic_new_old
                    IS
                    PROCEDURE create_column_names(p_table VARCHAR2);
                    PROCEDURE save_values (p_rowid ROWID) ;
                    PROCEDURE get_values (p_table varchar2);
                    TYPE type_values_new is table of varchar2(3000) index by binary_integer;
                    m_values_new type_values_new;
                    TYPE type_values_old is table of varchar2(3000) index by binary_integer;
                    m_values_old type_values_old;
                    m_rec_number binary_integer;

                    END;
                    /

                    CREATE OR REPLACE PACKAGE BODY pg_dynamic_new_old IS
                    m_table VARCHAR2 (30);
                    m_columns VARCHAR2 (32000);
                    --erhalten die records mit den Werten
                    TYPE type_cols IS TABLE OF VARCHAR2(3000) INDEX BY BINARY_INTEGER;
                    m_cols type_cols;
                    --
                    PROCEDURE create_column_names (p_table VARCHAR2)
                    IS
                    BEGIN
                    m_table := upper (p_table);
                    m_rec_number := 0;
                    FOR rec IN
                    (SELECT upper(hs_feld) column_name
                    FROM t_historiensteuerung
                    WHERE upper(hs_tabelle) = m_table)
                    LOOP
                    m_rec_number := m_rec_number + 1;
                    m_cols(m_rec_number) := 'v_record.' || rec.column_name;
                    END LOOP;
                    END;
                    --
                    PROCEDURE save_values (p_rowid ROWID)
                    AS
                    v_stat_new VARCHAR2 (32000);
                    v_stat_old varchar2(32000);
                    BEGIN
                    for n in 1 .. m_rec_number
                    loop
                    v_stat_new :=
                    ' DECLARE ' ||
                    ' PRAGMA AUTONOMOUS_TRANSACTION;' ||
                    ' v_record ' || m_table || '%ROWTYPE;' ||
                    ' BEGIN' ||
                    ' SELECT * INTO v_record' ||
                    ' FROM ' || m_table ||
                    ' WHERE ROWID = ''' || p_rowid || ''';' ||
                    ' pg_dynamic_new_old.m_values_old(:1) := ' || m_cols(n) || ';'||
                    ' END;';
                    execute immediate v_stat_new using n;
                    end loop;
                    for n in 1 .. m_rec_number
                    loop
                    v_stat_new :=
                    ' DECLARE ' ||
                    ' v_record ' || m_table || '%ROWTYPE;' ||
                    ' BEGIN' ||
                    ' SELECT * INTO v_record' ||
                    ' FROM ' || m_table ||
                    ' WHERE ROWID = ''' || p_rowid || ''';' ||
                    ' pg_dynamic_new_old.m_values_new(:1) := ' || m_cols(n) || ';'||
                    ' END;';
                    execute immediate v_stat_new using n;
                    end loop;
                    for n in 1 .. m_rec_number
                    loop
                    if nvl(pg_dynamic_new_old.m_values_old(n),0) != nvl(pg_dynamic_new_old.m_values_new(n),0) THEN
                    insert into t_historie (hi_betreffende_tabelle,hi_betreffendes_feld,hi_wert_alt,hi_wert_neu)
                    values (m_table,substr(m_cols(n),10),pg_dynamic_new_old.m_values_old(n),pg_dynamic_new_old.m_values_new(n));
                    END IF;
                    end loop;
                    EXCEPTION
                    WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE (sqlerrm);
                    END;
                    --
                    procedure get_values (p_table VARCHAR2) as
                    v_current_rowid ROWID;
                    BEGIN
                    pg_dynamic_new_old.create_column_names(p_table);
                    WHILE pg_plsql_table.rowid_exists LOOP
                    v_current_rowid := pg_plsql_table.current_rowid;
                    pg_dynamic_new_old.save_values (v_current_rowid);
                    END LOOP;
                    END;
                    END;
                    /

                    CREATE OR REPLACE TRIGGER bur_hrue_test
                    BEFORE UPDATE ON hrue_test
                    FOR EACH ROW
                    BEGIN
                    pg_plsql_table.populate_with_rowid (:OLD.ROWID);
                    END;
                    /

                    CREATE OR REPLACE TRIGGER aus_hrue_test
                    AFTER UPDATE ON hrue_test
                    BEGIN
                    pg_dynamic_new_old.get_values('hrue_test');
                    pg_plsql_table.clear;
                    END;
                    /

                    • 7. re:Dynamic references to trigger vars :new and :old
                      34946
                      While this code seems to be extremely creative, it is all done with dynamic sql.
                      It seems that while getting out of writing audit code,
                      this will create some severe performance penalties, especially when used to audit high transaction volume tables.
                      • 8. re:Dynamic references to trigger vars :new and :old
                        18338
                        Many thanks for your compliment ;-)

                        But do you have an alternative?
                        • 9. re:Dynamic references to trigger vars :new and :old
                          34946
                          The solution is to write audit triggers for any tables you want such a thorough audit on.
                          The advantages you gain from not using dynamic plsql with every update/ins/delete to audited tables will be significant.
                          Using dynamic blocks of plsql called from any dml activity will definitely impact your v$sqlarea hit ratio,
                          and the overall speed of any large dml activities.

                          If maintaining these audit triggers seems like a headache,
                          create a package that will generate your audit triggers for you.
                          Basically, you can use code very similar to the above, but use it to generate ddl for the triggers.

                          If the tables are changed you can then regenerate the triggers to audit them.
                          You could possibly use schema level triggers to sense a change and reconstruct the audit triggers dynamically.
                          That way the triggers would always reflect the current schema, BUT I WOULD NOT RECOMMEND USING THIS APPROACH.

                          I would probably just have the create_audit_triggers package output the scripts to drop the current triggers and create the rebuilt triggers.
                          That way you can actually control them, especially if there are several column changes to a table that is
                          full of data (where changes may be implemented one at a time).
                          You wouldnt want the dba to get confused/annoyed by the trigger_creation package reconstructing the triggers with each ddl change that was made.
                          Since the triggers are such an important structural part of the database,
                          maintaining some kind of manual control over when and how they are created is important.



                          • 10. re:Dynamic references to trigger vars :new and :old
                            Barbara Boehmer
                            Here is a method by Tom Kyte:


                            http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:59412348055
                            • 11. Re: re:Dynamic references to trigger vars :new and :old
                              Saikumar Konduru
                              Hi Henrik,

                              Requirement : Whenever data gets updated in a table ex: 'XEMP', XEMP_AUDIT table (audit table of XEMP) should get populated where as the structure of XEMP_AUDIT table is as below.

                              desc xemp_audit
                              Name Null Type
                              ------- ---- ------------
                              TNAME CHAR(4)
                              COLNAME VARCHAR2(30)
                              OLDVAL VARCHAR2(30)
                              NEWVAL VARCHAR2(30)


                              I followed your code and build the below trigger on table XEMP and its compiled successfully .


                              create or replace trigger xxtest
                              after update on xemp
                              for each row
                              declare
                              TYPE type_cols IS TABLE OF VARCHAR2(3000) INDEX BY BINARY_INTEGER;
                              TYPE type_values_new is table of varchar2(3000) index by binary_integer;
                              m_values_new type_values_new;
                              TYPE type_values_old is table of varchar2(3000) index by binary_integer;
                              m_values_old type_values_old;
                              m_cols type_cols;
                              m_table VARCHAR2 (30);
                              m_rec_number binary_integer;
                              v_stat_new VARCHAR2 (32000);
                              l_rowid NUMBER;
                              m_rowid NUMBER;
                              i NUMBER;
                              j NUMBER;
                              begin
                              m_table := '&table_name';
                              m_rec_number := 0;
                              FOR rec IN
                              (SELECT column_name
                              FROM user_tab_columns
                              WHERE upper(table_name) = upper(m_table))
                              LOOP
                              m_rec_number := m_rec_number + 1;
                              m_cols(m_rec_number) := 'v_record.' || rec.column_name;
                              END LOOP;

                              l_rowid := 0;
                              i := 0;
                              for n in 1 .. m_rec_number
                              loop
                              l_rowid := l_rowid + 1;
                              i := i + 1;
                              v_stat_new :=
                              ' DECLARE ' ||
                              ' PRAGMA AUTONOMOUS_TRANSACTION;' ||
                              ' v_record ' || m_table || '%ROWTYPE;' ||
                              ' BEGIN' ||
                              ' SELECT * INTO v_record' ||
                              ' FROM ' || m_table ||
                              ' WHERE ROWID = ''' || l_rowid || ''';' ||
                              ' m_values_old(i) := ' || m_cols(n) || ';'||
                              ' END;';
                              execute immediate v_stat_new using n;
                              end loop;
                              m_rowid := 0;
                              j := 0;
                              for n in 1 .. m_rec_number
                              loop
                              m_rowid := m_rowid + 1;
                              j := j + 1;
                              v_stat_new :=
                              ' DECLARE ' ||
                              ' v_record ' || m_table || '%ROWTYPE;' ||
                              ' BEGIN' ||
                              ' SELECT * INTO v_record' ||
                              ' FROM ' || m_table ||
                              ' WHERE ROWID = ''' || m_rowid || ''';' ||
                              ' m_values_new(:1) := ' || m_cols(n) || ';'||
                              ' END;';
                              execute immediate v_stat_new using n;
                              end loop;
                              for n in 1 .. m_rec_number
                              loop
                              --if nvl(m_values_old(n),0) != nvl(pg_dynamic_new_old.m_values_new(n),0) THEN
                              insert into xemp_audit (tname,colname,oldval,newval)
                              values (m_table,substr(m_cols(n),10),m_values_old(n),m_values_new(n));
                              --END IF;
                              end loop;
                              end;
                              /


                              While testing the trigger, its throwing an error when i run the query "update xemp set empno = 13 where ename is null and deptno = 500;"
                              and the error is as below :

                              Error starting at line 45 in command:
                              update xemp set empno = 13 where ename is null and deptno = 500
                              Error report:
                              SQL Error: ORA-06550: line 1, column 123:
                              PLS-00201: identifier 'M_VALUES_OLD' must be declared
                              ORA-06550: line 1, column 123:
                              PL/SQL: Statement ignored
                              ORA-06512: at "XXDEV01.XXTEST", line 43
                              ORA-04088: error during execution of trigger 'XXDEV01.XXTEST'
                              06550. 00000 - "line %s, column %s:\n%s"
                              *Cause:    Usually a PL/SQL compilation error.
                              *Action:


                              /

                              Could you please help
                              • 12. Re: re:Dynamic references to trigger vars :new and :old
                                AlbertoFaenza
                                Hi Sakumar,

                                please don't hijack a 10 years old thread.

                                If you have any question please open a new thread.

                                Please read SQL and PL/SQL FAQ

                                Additionally when you put some code please enclose it between two lines starting with {noformat}
                                {noformat}
                                i.e.:
                                {noformat}
                                {noformat}
                                SELECT ...
                                {noformat}
                                {noformat}
                                
                                Regards.
                                Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                • 13. Re: Dynamic references to trigger vars :new and :old
                                  BluShadow
                                  As Alberto says, start your own thread for your own question.

                                  Locking this thread