Home » SQL & PL/SQL » SQL & PL/SQL » Trigger created with compilation error "column not allowed here" (Oracle Database 10.2.0 g Express Edition, Windows XP)
Trigger created with compilation error "column not allowed here" [message #336491] Sun, 27 July 2008 20:46 Go to next message
marina77
Messages: 1
Registered: July 2008
Junior Member

I can't create my trigger without getting the compilation error "column not allowed here".
The weirdest thing is that it complains about the line "for each row"...

Here is the Script:


drop table balance;
drop table transaction;

variable v_amount number;

create table balance(
account# number primary key,
name varchar(20),
bal number);

create table transaction(
trans# number primary key,
account# number,
amount number,
vdate date,
details varchar(15));

create sequence TR start with 20;


create or replace procedure add_bal(acc number, nam varchar, balance number)
is
begin
insert into balance(account#, name, bal)
values(acc, nam, balance);
commit;
end;
/

execute add_bal(100, 'ASLI', 2500);
execute add_bal(101, 'SMITH', 750);
execute add_bal(102, 'KIM', 700);
/

create or replace procedure bal(acc# number, amount number)
is
v_bal number;

begin
select b.bal into v_bal from balance b where b.account#=acc#;

if(amount<=v_bal) then
update balance
set bal=bal-amount where balance.account#=acc#;

insert into transaction
values(TR.nextval, acc#, amount, sysdate, 'PROCESSED');
else
update balance
set bal=bal-amount where balance.account#=acc#;
end if;

commit;
end;
/

create or replace function int_rate(acco# number)
return number
is
v_bal number;
begin
select bal into v_bal from balance where account#=acco#;
if (v_bal>3000) then return 0.08;
elsif(v_bal between 1000 and 3000) then return 0.06;
elsif(v_bal<1000) then return 0.04;
end if;
end;
/

create or replace trigger upd_balance
before update on balance
for each row
when(new.bal<0)
begin
INSERT INTO transaction
VALUES (TR.nextval, :old.account#, (:old.bal-:new.bal), sysdate, "denied");
RAISE_APPLICATION_ERROR(-20000, 'NOT ALLOWED');
END;
/

And this is what I get when I run the script:

Warning: Trigger created with compilation errors.

SQL> show error
Errors for TRIGGER UPD_BALANCE:

LINE/COL ERROR
-----------------------------------------------------
2/1 PL/SQL: SQL Statement ignored
3/66 PL/SQL: ORA-00984: column not allowed here
SQL> list
1 create or replace trigger upd_balance
2 before update on balance
3 for each row
4 when(new.bal<0)
5 begin
6 INSERT INTO transaction
7 VALUES (TR.nextval, :old.account#, (:old.bal-:new.bal), sysdate, "denied");
8 RAISE_APPLICATION_ERROR(-20000, 'NOT ALLOWED');
9* END;

What might cause the problem here?
Thanks in advance!
Re: Trigger created with compilation error "column not allowed here" [message #336492 is a reply to message #336491] Sun, 27 July 2008 20:51 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above

Moving to APEX subforum
Re: Trigger created with compilation error "column not allowed here" [message #336551 is a reply to message #336491] Mon, 28 July 2008 02:13 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
'denied' needs to be enclosed in single quotes, not double quotes, otherwise it think it is a column name that does not exist.

[Updated on: Mon, 28 July 2008 02:14]

Report message to a moderator

Re: Trigger created with compilation error "column not allowed here" [message #336725 is a reply to message #336492] Mon, 28 July 2008 15:43 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
anacedent wrote on Mon, 28 July 2008 03:51
Moving to APEX subforum

Why? ./fa/1600/0/
Re: Trigger created with compilation error "column not allowed here" [message #336749 is a reply to message #336725] Mon, 28 July 2008 17:12 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Littlefoot wrote on Mon, 28 July 2008 13:43
anacedent wrote on Mon, 28 July 2008 03:51
Moving to APEX subforum

Why? ./fa/1600/0/


(Oracle Database 10.2.0 g Express Edition, Windows XP)

Isn't "Express Edition" same as APEX?
Re: Trigger created with compilation error "column not allowed here" [message #336756 is a reply to message #336749] Mon, 28 July 2008 21:41 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
anacedent wrote on Mon, 28 July 2008 15:12

Isn't "Express Edition" same as APEX?


No, APEX (Application Express) can be downloaded separately to work with Oracle 11g Enterprise Edition. It comes with 10g Express Edition without a separate download. 10g Express Edition users can use SQL*Plus just like Enterprise Edition users. APEX is sort of a miniature substitute for the Developer Suite (Forms and Reports). I believe the original intent was to be able to develop things on a small scale in APEX and also to be able to port those over to the larger Forms and Reports modules. So, questions that are specific to that portion would be appropriate for the APEX forum, but general SQL and PL/SQL questions are more appropriately posted in the SQL and PL/SQL forum. So, I'll move it there.

Previous Topic: ORA-01427: single-row subquery returns more than one row
Next Topic: Dependencies
Goto Forum:
  


Current Time: Sat Dec 10 08:58:00 CST 2016

Total time taken to generate the page: 0.10207 seconds