We help IT Professionals succeed at work.

usage of execute immediate in triggers, problem in using :new values

sakthikumar
sakthikumar asked
on
I have a trigger, where In, I tried to use the below statement.
but I m getting bad bind variable.

execute immediate 'update table table_name set columnname = ":new.column1";

If this is not possible in triggers, Is there any other alternate way?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
First: no double quotes in Oracle.  Ever.

Try:
execute immediate 'update table table_name set columnname = '':myval''' using :new.column1;

Author

Commented:
This will work but
The problem is I only know the column name during runtime.

In that case how to resolve this?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>I only know the column name during runtime.

I do not understand what you are saying.  Can you provide a little more information?

Author

Commented:
Actually the problem is I dont know the name of the column in the design time.

refer below eg:
for i in (select column_name from all_tab_cols where column_name like '%ABC')
loop
execute immediate 'update table_name set :new.'|| i.column.name||' = ''abc''';
end loop;
I am not able to execute this kind of statement in trigger. You have any idea or solution for this.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>is I dont know the name of the column in the design time.

What is the requirement here?

Writing a trigger to perform some task on some column that you don't seems like a very unlikely requirement and pretty much impossible.

What if this 'unknown' column is a DATE?  You cannot set it to 'abc'.

Author

Commented:
I've requested that this question be deleted for the following reason:

My requirement is wrong. please ignore. This cannot be solved in triggers.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I'm afraid I'll need to object.  The question as asked was answered in http:#a37086775

Explore More ContentExplore courses, solutions, and other research materials related to this topic.