variable issue in trigger oracle 11gr2

jl66
jl66 used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
No, you cannot do that.

No need for a lot of if statements:

 :new.c1 := nvl(:new.c1,'N');
 :new.c2 := nvl(:new.c2,'N');
 :new.c3 := nvl(:new.c3,'N');
Most Valuable Expert 2011
Top Expert 2012
Commented:
use table default values instead of a trigger.  Then you don't have to code for them at all and it will be more efficient execution as well


create table scott.T( c1 varchar2(30) default 'abc',
c2 varchar2(10) default 'def',
 c3 varchar2(20) default 'xyz');


defaults don't apply on update,  but your trigger is only for insert too, so same functionality, but better implementation

defaults do apply to inserts after triggers fire,  so, if you have other logic in your trigger that is dependent on the values, then you will need to assign them inside the trigger.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Defaults may not work if the actual insert provides the null:
insert into t values(null,null);
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

johnsoneSenior Oracle DBA
Commented:
You cannot do what you are trying to do.  The resolution of :NEW.<col> must be available at compile time.  You cannot use a dynamic name like that.

I would say you have 2 main options.

Use a combination of a BEFORE INSERT and AFTER STATEMENT triggers to do the update with dynamic SQL.  Do a search on mutating table error.  The methodology for the triggers is the same.
Don't use dynamic SQL.  Let SQL write the trigger for you

My preferred method would be the second way.

Essentially, you run a query like this:
SELECT '  :new.' 
       || column_name 
       || ' := NVL(:new.' 
       || column_name 
       || ',''N'');' 
FROM   all_tab_columns 
WHERE  owner = 'SCOTT' 
       AND table_name = 'T'; 

Open in new window

The results of that query is the body of your trigger.  Add the CREATE before it and the END after it.  Then you have your trigger.

Of course, this assumes that the type of the column is character.
Most Valuable Expert 2011
Top Expert 2012
Commented:
>>> Defaults may not work if the actual insert provides the null:

true,  I was trying to include the caveats, but I missed that one


12c does include an "ON NULL" option for defaults

CREATE TABLE t
(
    c1 VARCHAR2(30) DEFAULT ON NULL 'abc',
    c2 VARCHAR2(10) DEFAULT ON NULL 'def',
    c3 VARCHAR2(20) DEFAULT ON NULL 'xyz'
);

INSERT INTO t (c1,c2)
     VALUES (NULL, NULL);   -- two columns pick up default because of ON NULL, third column defaults because unspecified

SELECT * FROM t;

C1                             C2         C3                  
------------------------------ ---------- --------------------
abc                            def        xyz
jl66Consultant

Author

Commented:
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?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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.
johnsoneSenior Oracle DBA

Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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.
johnsoneSenior Oracle DBA

Commented:
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.
jl66Consultant

Author

Commented:
Thanks a lot for everyone's help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial