Avatar of jl66
jl66
Flag for United States of America asked on

variable issue in trigger oracle 11gr2

Want to write a trigger like below. Is it possible? Definitely it is compiled with errors.

CREATE OR REPLACE TRIGGER SCOTT.T_TRG BEFORE INSERT ON SCOTT.T
FOR EACH ROW
begin
  for i in ( select column_name from all_tab_columns where owner = 'SCOTT' and table_name = 'T')
  loop
     if :new.i is null then
       :new.i := 'N';
     end if;
  end loop;
END;
/

The reason to use the variable i is there are many columns in T in real situation, so if writing the 'if' block, that would be too many. Take an example,  we can assume there are 3 columns in T like below
create table scott.T( c1 varchar2(30), c2 varchar2(10), c3 varchar2(20)); How to use the variable in this case?
Can any gurus shed some light on it?
Oracle Database

Avatar of undefined
Last Comment
jl66

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Sean Stuber

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
slightwv (䄆 Netminder)

Defaults may not work if the actual insert provides the null:
insert into t values(null,null);
SOLUTION
johnsone

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Sean Stuber

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
jl66

ASKER
Thanks a lot for everyone's tips.
--slightwv: improved and good for none of 'if' block, but still need to list each one of columns in the table.
--sdstuber: already used default,but encountered the situation slightwv pointed out. 12c has improved it, but currently 11gr2.
--johnsone: tried to write the statements as you listed, but got the errors below:

"[Warning] ORA-24344: success with compilation error
28/1    PLS-00428: an INTO clause is expected in this SELECT statement
 (1: 0): Warning: compiled but with compilation errors"

Could you please give me a complete example?
slightwv (䄆 Netminder)

You cannot dynamically do what you want.

>>Could you please give me a complete example?

That was working code.  What it was meant to do was generate the statements necessary for you to copy and paste into your trigger.

It wasn't meant to be placed into your trigger.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
johnsone

What I gave you would be the total body of the code.  It should be this:
CREATE OR REPLACE TRIGGER SCOTT.T_TRG BEFORE INSERT ON SCOTT.T
FOR EACH ROW
begin

   ....  paste output from query here  ...

END;
/

Open in new window

The error message you posted isn't helpful as there are no select statements in what I gave you.
slightwv (䄆 Netminder)

>>The error message you posted isn't helpful as there are no select statements in what I gave you

Makes perfect sense if the select you provided was pasted into the trigger body as-is.
johnsone

I guess, but I specifically stated:

>> The results of that query is the body of your trigger.

The result is the body, not the query.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
jl66

ASKER
Thanks a lot for everyone's help.