Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
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?
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?
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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.
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.
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.
My preferred method would be the second way.
Essentially, you run a query like this:
Of course, this assumes that the type of the column is character.
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';
Of course, this assumes that the type of the column is character.
>>> 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
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
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: 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?
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.
>>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.
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;
/
>>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.
Makes perfect sense if the select you provided was pasted into the trigger body as-is.
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.
>> The results of that query is the body of your trigger.
The result is the body, not the query.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2016 Part … 212 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - PowerPoint 2016… 224 lessons
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
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');