Re: Triigger: Referencing Items Indirectly in PL/SQL

In article <6j9pfo$64k$1_at_nnrp1.dejanews.com>, <gcwong_at_interlog.com> wrote:

> Within a database trigger, I'm attempting to indirectly reference
> the correlation variables :new.columnX and :old.columnX. I've used
> the code from RevealNet and hard-coded it into my database trigger.
>
> Logical View of what I'm trying is this:
>
>new_col_value := :new.col_name
>where col_name is fetched from user_tab_columns
>
>Actual Code:
>DBMS_SQL.PARSE(sql_cursor, 'BEGIN :val := ' || ':new.'|| col_name || ';
>END;', DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(sql_cursor, 'val', 'a',
>2000); sql_ret := DBMS_SQL.EXECUTE(sql_cursor);
>DBMS_SQL.VARIABLE_VALUE(sql_cursor,'val', new_col_value);

I don't there is any way possible to indirectly access the :new and :old values.

DBMS_SQL will certainly not help--it is designed to get you the current values from the database, which would be the same as the :old values. But in your trigger, the :new values haven't been posted to the database, so no other utility would ever be able to find them.

Just curious... why do you want to use dynamic sql anyway? Do you have hundreds of columns in your table, or what?

Regards,
Steve Cosner Received on Wed May 13 1998 - 00:00:00 CEST