| Trigger Unhappy [message #38749] |
Sat, 11 May 2002 01:19  |
Diana
Messages: 17
Registered: February 1999
|
Junior Member |
|
|
Hello,
This is my first attempt at creating a trigger (for an assignment) and it's got compilation errors.
I've included the code for your perusal. Any suggestions?
Thanks for your help in advance.
Diana
CREATE TRIGGER check_pnr_no before insert or update on passenger for each row when (length(new.pnr_no) != 8)
BEGIN
/* get the length of the pnr_no field and raise error */
Select pnr_no from passenger where length(pnr_no) != 8
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20100, 'TRIGGER: check_pnr_no - Passenger Number not long enough')
END check_pnr_id;
Warning: Trigger created with compilation errors.
|
|
|
|
| Re: Trigger Unhappy [message #38752 is a reply to message #38749] |
Sat, 11 May 2002 17:20  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Diana, first of all, you can see the actual compilation error(s) in SQL*Plus after trying the compile by entering: show errors
As far as your code:
1) Always specify CREATE OR REPLACE so you can compile a new version of your trigger without having to first drop the previous version.
2) On a FOR EACH ROW trigger, you already have the values of the columns loaded in the :new.column_name and :old.column_name placeholders - you don't select the values from the table as you were trying to do.
So, here is a revised version (as an example, I threw in a :new reference there to show the incorrect length):
create or replace trigger check_pnr_no
before insert or update on passenger
for each row
when (length(new.pnr_no) != 8)
begin
raise_application_error(-20100, 'TRIGGER: check_pnr_no - Passenger Number is ' || length(:new.pnr_no) || ' positions instead of 8 positions');
end; |
|
|
|
| Re: Trigger Unhappy [message #38756 is a reply to message #38749] |
Sun, 12 May 2002 18:29 |
Diana
Messages: 17
Registered: February 1999
|
Junior Member |
|
|
It works!!!
Thanks so much Todd.
These triggers are hard to get a handle on.
1) I am wondering why you didn't use the
EXCEPTION
WHEN OTHERS THEN
statements
2) I thought it was 'better' if you used
END check_pnr_id;
rather than a plain END.
Thanks again,
Diana
|
|
|
|
| Re: Trigger Unhappy [message #38761 is a reply to message #38749] |
Mon, 13 May 2002 09:22 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
I pulled out the exception handling because there is no possible exception to handle in this trigger. You aren't doing a SELECT/INTO, a conversion, or any DML - the common things that would normally possibly raise an exception and would necessitate exception handling.
As far as the object name after the END, I pulled it out for two reasons: 1) It doesn't really add any benefit, but there is certainly no harm in including it, and 2) the name you had (check_pnr_id) didn't match the name of the trigger (check_pnr_no).
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
