Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2008
    Posts
    17

    Unanswered: Using variables with :new and :old specifiers of a trigger

    Can I use variables with :new and : old specifiers of a trigger? E.g. A trigger has a variable named column_name that holds the name of the column for the triggered table. Now, can I use :new.column_name and : old .column_name? I tried using it, I received the error :- Bad bind variable. How can I achieve the desired functionality?

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    > Can I use variables with :new and : old specifiers of a trigger?
    No

    > How can I achieve the desired functionality?
    You may create script for generating the trigger, as shown in http://asktom.oracle.com/pls/asktom/...14919324034227.
    Maybe Fine Grain Auditing could be helpful; just guessing as you did not specify the "desired functionality".

  3. #3
    Join Date
    Jun 2008
    Posts
    17

    Example problem

    create or replace trigger trg_col after update on account
    for each row
    declare
    column_changed varchar2(100):=' ';
    cur_column varchar2(25);
    v_prefix varchar2(10):=':new.';
    v_prefix1 varchar2(10):='ld.';
    CURSOR cur_col_audit IS
    SELECT column_name
    FROM user_tab_columns
    WHERE lower(table_name) = account'
    AND column_name NOT IN
    ('AUD_ACTION','AUD_TIMESTAMP','AUD_USER')
    ORDER BY column_id;
    begin
    for i in cur_col_audit loop
    cur_column:=trim(i.column_name);
    --dbms_output.put_line(cur_column);
    --dbms_output.put_line(v_prefix||cur_column);
    if(v_prefix||cur_column != v_prefix1||cur_column) then
    dbms_output.put_line(v_prefix||cur_column);
    --column_changed:= column_changed||' , '||:new.(i.column_name);
    end if;
    end loop;
    end;
    I want to get the value for :new.account_no when specifying 'v_prefix||cur_column' in dbms_output.put_line(v_prefix||cur_column). Where account_no is the column of the table account . Please suggest. In the mean time I will check the link sent.

Posting Permissions

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