Dynamic sql in trigger body
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Dynamic sql in trigger body

Hybrid View

  1. #1
    Join Date
    May 2002
    Location
    Mumbai, India
    Posts
    27

    Unhappy

    Hi,
    I want to write a DB triger on a table which will trap the
    column-name with old and new values which are updated.
    The trigger should be written in a dynamic way to incorporate
    any alteration (addition or deletion of columns) on the table.

    Suppose the sal and deptno field of EMP table are updated.
    Then the trigger will insert the column-name (sal, deptno)
    along with old and new values into an another table.

    So how to write this dynamic sql !! I've used dynamic sql
    to achieve this, but while parsing the statement it throws
    an error 'Bind Variable not declared' for the ld and :new
    specifiers for the trigger.


  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Post your code, please.
    Ales

  3. #3
    Join Date
    May 2002
    Location
    Mumbai, India
    Posts
    27
    Hi Ales,
    Here is the code. Please go thru it and advice me what to do.
    Thanks in advance ....
    ---------------------------------------------------------
    create table ak_test (
    id number(9),
    name varchar2(50),
    address varchar2(50),
    pin number(10),
    tel_no number(20),
    primary key (id));

    create table ak_aud (
    id number(9),
    col_name varchar2(30),
    old_value varchar2(100),
    new_value varchar2(100));
    ------------------------------------------------
    create or replace trigger ak_trig
    before update on ak_test
    for each row
    DECLARE
    d_cid integer;
    d_exe integer;
    d_str varchar2(1000);
    old_value varchar2(50);
    new_value varchar2(50);

    cursor c1 is select column_name col_name from user_tab_columns
    where table_name = 'AK_TEST';

    BEGIN
    FOR r1 in c1 LOOP
    d_str := 'select :old.'||r1.col_name||' , :new.'||r1.col_name||' from dual' ;

    d_cid := dbms_sql.open_cursor;
    dbms_sql.parse(d_cid,d_str,dbms_sql.v7);

    dbms_sql.define_column(d_cid,1,old_value,50);
    dbms_sql.define_column(d_cid,2,new_value,50);

    d_exe := dbms_sql.execute(d_cid);

    loop
    if dbms_sql.fetch_rows(d_cid) = 0 then
    exit;
    end if;

    dbms_sql.column_value(d_cid,1,old_value);
    dbms_sql.column_value(d_cid,2,new_value);

    end loop;

    dbms_sql.close_cursor(d_cid);

    if old_value <> new_value then

    insert into ak_aud values(:old.id,r1.col_name,old_value,new_value);

    end if;

    END LOOP;
    END;
    /

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Hi,
    you cannot use the :new. and :old. reference in dynamic sql as it is a notation for bind variables.

    I don't think this way is possible. You will have to "hardcode" the test for each column in the table and modify the trigger if structure of the table changes.

    Ales


    [Edited by ales on 05-15-2002 at 09:23 AM]

  5. #5
    Join Date
    May 2002
    Location
    Mumbai, India
    Posts
    27
    Dear Ales,
    The hardcoding will not be effective if the table is altered.
    So that is not a good practice.
    Can you suggest some other way out ??
    Thanks

  6. #6
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hi
    u hv to do something like this
    i this example the trigger update the audit table where therez an update or delete in the REF_SEC_SALE table

    CREATE OR REPLACE TRIGGER T_U_D_REF_SEC_SALE
    AFTER UPDATE OR DELETE ON REF_SEC_SALE
    FOR EACH ROW
    DECLARE
    --Variable Declaration
    lv_aud_seq_no NUMBER;
    lv_tab_name VARCHAR2(30) := 'REF_SEC_SALE';

    BEGIN
    SELECT seq_sec_audit_id.NEXTVAL
    INTO lv_aud_seq_no FROM dual;

    IF UPDATING THEN

    INSERT INTO T_SEC_AUDIT_TRAIL(
    AUD_SEQ_NO, TABLE_NAME, COLUMN_NAME, OLD_VALUE,
    NEW_VALUE, TRIGGER_FLAG,CREATE_DATE, CREATE_USER)
    VALUES (lv_aud_seq_no, lv_tab_name, 'COUNTRYCODE',
    :OLD.COUNTRYCODE, :NEW.COUNTRYCODE, 'U', SYSDATE, USER );

    IF :NEW.EFFECTIVE_DATE != :OLD.EFFECTIVE_DATE THEN
    INSERT INTO T_SEC_AUDIT_TRAIL(
    AUD_SEQ_NO, TABLE_NAME, COLUMN_NAME, OLD_VALUE,
    NEW_VALUE, TRIGGER_FLAG,CREATE_DATE, CREATE_USER)
    VALUES (lv_aud_seq_no, lv_tab_name, 'EFFECTIVE_DATE',
    :OLD.EFFECTIVE_DATE, :NEW.EFFECTIVE_DATE, 'U', SYSDATE, USER);
    END IF;

    IF :NEW.STATUS != :OLD.STATUS THEN
    INSERT INTO T_SEC_AUDIT_TRAIL(
    AUD_SEQ_NO, TABLE_NAME, COLUMN_NAME, OLD_VALUE,
    NEW_VALUE, TRIGGER_FLAG,CREATE_DATE, CREATE_USER)
    VALUES (lv_aud_seq_no, lv_tab_name, 'STATUS',
    :OLD.STATUS, :NEW.STATUS, 'U', SYSDATE, USER);
    END IF;
    END IF;

    IF DELETING THEN

    INSERT INTO T_SEC_AUDIT_TRAIL(
    AUD_SEQ_NO, TABLE_NAME, COLUMN_NAME, OLD_VALUE,
    NEW_VALUE, TRIGGER_FLAG,CREATE_DATE, CREATE_USER)
    VALUES (lv_aud_seq_no, lv_tab_name, 'EFFECTIVE_DATE',
    :OLD.EFFECTIVE_DATE, NULL, 'D', SYSDATE, USER);

    INSERT INTO T_SEC_AUDIT_TRAIL(
    AUD_SEQ_NO, TABLE_NAME, COLUMN_NAME, OLD_VALUE,
    NEW_VALUE, TRIGGER_FLAG,CREATE_DATE, CREATE_USER)
    VALUES (lv_aud_seq_no, lv_tab_name, 'MATURITY_DATE',
    :OLD.MATURITY_DATE, NULL, 'D', SYSDATE, USER);

    INSERT INTO T_SEC_AUDIT_TRAIL(
    AUD_SEQ_NO, TABLE_NAME, COLUMN_NAME, OLD_VALUE,
    NEW_VALUE, TRIGGER_FLAG,CREATE_DATE, CREATE_USER )
    VALUES (lv_aud_seq_no, lv_tab_name, 'STATUS',
    :OLD.STATUS, NULL, 'D', SYSDATE, USER);
    END IF;

    END;

    rgds,
    Cheers!
    OraKid.

  7. #7
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Originally posted by Anomitro
    The hardcoding will not be effective if the table is altered.
    So that is not a good practice.
    Yes, I agree.
    In PL/SQL does not exist the access to metadata of a record (except the dbms_sql package). Here I mean that's not possible to get list of column names of a record, you always have to know column names if you write the code.
    I find it one of the most annoying features of PL/SQL.

    In addition, as I wrote earlier, it's not possible to use :new. and :old. records in dynamic SQL as it always thinks they're bind variables.

    Thus, I don't believe there's a solution of your problem.

    From my point of view, alteration of the table shouldn't be very frequent and auditing is not a vital issue, so I'd suggest put up with that. Write it in the best way that Oracle makes possible and hardcode it.

    And alterations of the table? It's another item to remember. Anyway, there are more things that don't work automatically ;-).

    Good luck!

    Ales



Posting Permissions

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



Click Here to Expand Forum to Full Width