This discussion is archived
13 Replies Latest reply: Nov 23, 2012 2:11 AM by BluShadow RSS

Dynamic references to trigger vars :new and :old

15060 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Many thanks for your compliment ;-)

    But do you have an alternative?
  • 9. re:Dynamic references to trigger vars :new and :old
    34946 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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
    975718 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    As Alberto says, start your own thread for your own question.

    Locking this thread