Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL trigger (10.2.0.2.0)
PL/SQL trigger [message #392595] Wed, 18 March 2009 10:24 Go to next message
jordz1986
Messages: 6
Registered: March 2009
Junior Member
Hello,
Firstly I am very new to PL/SQL, and am trying to create a trigger that generates an exception when 'MaxBooks' exceeds 5, this is working fine. However I need the exception message to return 'BorName'.

Here is the code

create or replace trigger checkloans
before insert or update on loan
for each row
declare
 maxbooks number;
 bor varchar2(20);
 ex exception;
 
begin
 select count(*) into maxbooks
 from loan, borrower
 where loan.borid = borrower.borid
   and BorId = :new.BorId
   and borrower.Borname = :new.BorName;
   
 if maxbooks < 5 then
  dbms_output.put_line('Table Updated');
 else
  raise ex;
 end if;
 exception
 when ex then
  raise_application_error(-20000, 'Borrower ID:'|| :new.BorId || ' Name:' ||:new.BorName||' has 5 books already out on loan, update unsuccessful');
end;
/


unfortunately since trying this I am recieving the following message when compiling

11/27 PLS-00049: bad bind variable 'NEW.BORNAME'
20/77 PLS-00049: bad bind variable 'NEW.BORNAME'

I'm not really sure what this error means or what i can do to fix, please help.

Thanks

[Mod-Edit: Frank changed [i]-tags into [code]-tags to improve readability]

[Updated on: Wed, 18 March 2009 10:31] by Moderator

Report message to a moderator

Re: PL/SQL trigger [message #392599 is a reply to message #392595] Wed, 18 March 2009 10:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Is borname a column in the loan-table?

As an extra tip: google for mutating table error. Your trigger will suffer from that.
Re: PL/SQL trigger [message #392602 is a reply to message #392599] Wed, 18 March 2009 10:42 Go to previous messageGo to next message
jordz1986
Messages: 6
Registered: March 2009
Junior Member
borname belongs to the borrower table.
Will look at mutating table errors.

Thanks
Re: PL/SQL trigger [message #392608 is a reply to message #392602] Wed, 18 March 2009 11:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your trigger is on the LOAN table - the only :NEW parameters available to the trigger are for columns in the LOAN table - as no changes are being made to BORROWERS, you can't have a :NEW value for a Borrowers column
Re: PL/SQL trigger [message #392609 is a reply to message #392608] Wed, 18 March 2009 11:09 Go to previous messageGo to next message
jordz1986
Messages: 6
Registered: March 2009
Junior Member
OK, thanks for your help
Re: PL/SQL trigger [message #392612 is a reply to message #392609] Wed, 18 March 2009 11:26 Go to previous messageGo to next message
jordz1986
Messages: 6
Registered: March 2009
Junior Member
I'm struggling to find away to return the BorName, how would i go about doing this.

Thanks
Re: PL/SQL trigger [message #392613 is a reply to message #392595] Wed, 18 March 2009 11:31 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You'd have to SELECT it out of the borrower table if that's where it lives.
But I'd just stop trying to use a trigger to do this - you will run into mutating table if you continue along this line.
It's far simpler and easier to implement this kind of logic in a stored procedure.
Re: PL/SQL trigger [message #392641 is a reply to message #392599] Wed, 18 March 2009 13:37 Go to previous messageGo to next message
jordz1986
Messages: 6
Registered: March 2009
Junior Member
Thanks very much have sorted it now by adding another select statement that returns the borname

begin
select count(*) into maxbooks
from loan l, borrower b
where l.BorId = :new.BorId;

select borname into bor
from borrower
where :new.borId = borid;

[Updated on: Wed, 18 March 2009 13:38]

Report message to a moderator

Re: PL/SQL trigger [message #392816 is a reply to message #392641] Thu, 19 March 2009 06:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Nope - those aren't the queries you want.

That first query has a cartesian join between LOADN and BORROWER.

Plus - you've still not read up about MUTATING TABLE errors - if this trigger ever fires as a result of an update, or from an insert that wasn't an INSERT...VALUES.. then it's going to fail.
Re: PL/SQL trigger [message #392903 is a reply to message #392595] Thu, 19 March 2009 10:48 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Click on the link below for another way that uses a materialized view and doesn't have the problems associated with triggers.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:42304816945767
Previous Topic: ORA-29291: file remove operation failed (merged 3)
Next Topic: Question is simple but answer is confusing
Goto Forum:
  


Current Time: Fri Dec 09 13:36:16 CST 2016

Total time taken to generate the page: 0.11400 seconds