PL/SQL trigger [message #392595] |
Wed, 18 March 2009 10:24  |
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   |
Frank
Messages: 7901 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 #392608 is a reply to message #392602] |
Wed, 18 March 2009 11:08   |
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 #392613 is a reply to message #392595] |
Wed, 18 March 2009 11:31   |
cookiemonster
Messages: 13963 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   |
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   |
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.
|
|
|
|