dynamic :NEW

[rski]

how to make below trigger works

create or replace trigger .....

v1 varchar(50);
st varchar(50);
...
begin
....
v1=':NEW.'||'column_name';
execute immediate 'select '||v1||' from dual' into st
dbms_output.put_line(st);
....

the most important is that when defining trigger we don't know :NEW column names, and we generate it. I have a function that generate column names.
But the trigger i wrote do not work, the error message is

ORA-01008: not all variables bound

and it as about execute statement, cos trigger treats v1 as string not as :NEW.column_nam e
What i want to achieve is to write a trigger which is working even if i change column names.

[amitpatel66]

How are you passing the column name to this trigger from a function that you are talking about that generates a column?>

[rski]

How are you passing the column name to this trigger from a function that you are talking about that generates a column?>

the function reads colum name from system table and returns it as varchar.

[amitpatel66]

the function reads colum name from system table and returns it as varchar.

Could you please post your actual code for reference or a proper pseudo code for the same...Are you calling a function in the trigger and how you are doing that?

[rski]

here you are

a trigger def

create or replace trigger tg_qaz
        after insert
        on qaz
        for each row
        declare
        st varchar(100);
        v1 varchar(50);
begin
  v1:=':NEW.'||get_cols.get_col_name('QAZ',1);
  execute immediate  'select '||v1||' from dual ' into st;
  dbms_output.put_line(st);
end;

and the function, I skip the package definition

FUNCTION get_col_name(tab VARCHAR,num INTEGER) 
RETURN VARCHAR
IS
    CURSOR cols IS SELECT column_name FROM all_tab_columns WHERE
                          table_name=UPPER(tab);
    c all_tab_columns.column_name%TYPE;

        n INTEGER;
        BEGIN
                open cols;
                n:=1;
        LOOP
                FETCH cols INTO c;
                EXIT WHEN cols%NOTFOUND;
                IF (n=num) THEN
                        RETURN c;
                END IF;
                n:=n+1;
        END LOOP;
        dbms_output.put_line('There are not so many columns in '||UPPER(tab));
        RETURN NULL;
        END get_col_name;

remember, i'm plsql begginer :)

[amitpatel66]

How many columns you have in that table?

[rski]

How many columns you have in that table?

there are 2 columns in table

[amitpatel66]

there are 2 columns in table

Brilliant. Then you can make the things simple instaed of using a seperate function. You can select both the columns using :NEW.column1 and :NEW.column2 and use the one you require. The :NEW is not getting BIND to any value at run time so the error.

[rski]

Brilliant. Then you can make the things simple instaed of using a seperate function. You can select both the columns using :NEW.column1 and :NEW.column2 and use the one you require. The :NEW is not getting BIND to any value at run time so the error.

I'm afraid i wasn't clear enough. The problem is that i don't know what are (better say what will be in the future) the column names cos they may changed so I can't write
:NEW.i if column is 'i' because with time it may change to let say 'j'.
That's why i try to build :NEW. dynamically reading column names fro system table.
Is it clear? Maybe I do not understand your solution?

[amitpatel66]

I'm afraid i wasn't clear enough. The problem is that i don't know what are (better say what will be in the future) the column names cos they may changed so I can't write
:NEW.i if column is 'i' because with time it may change to let say 'j'.
That's why i try to build :NEW. dynamically reading column names fro system table.
Is it clear? Maybe I do not understand your solution?

The problem in using it this way is that the :NEW is not getting BIND to any value at runtime.

[rski]

The problem in using it this way is that the :NEW is not getting BIND to any value at runtime.

I know that, the question is if it is possible to resolve this problem in another way.