Home » SQL & PL/SQL » SQL & PL/SQL » Trigger problems
Trigger problems [message #232628] Sun, 22 April 2007 12:43 Go to next message
combitz
Messages: 2
Registered: April 2007
Location: UK
Junior Member
Hi All

I'm a student doing a 2nd year database module at the University of Wolverhampton so I would appreciate any help.
My Problem:
I'm trying to create a trigger using iSQL*plus and oracle 10g. I don't have to use the iSQL but its helpful as I'm currently working off site.

What I have so far.

The table
VOYAGE
VOYAGE_ID NOT NULL NUMBER(9)
CRUISE_ID NOT NULL NUMBER(6)
START_DATE NOT NULL DATE
DURATION NOT NULL NUMBER(2)
SHIP_NAME VARCHAR2(20)

voyage_id is obviously PK and cruise_id is a FK

I want a trigger that stops inserts into voyage if the month is november and the duration is 5.

what I have written

CREATE OR REPLACE TRIGGER cruise_november_trig
BEFORE INSERT ON voyage
FOR EACH ROW
WHEN (NEW.duration = 5)
DECLARE
jack_frost EXCEPTION;
BEGIN
IF (:NEW.SUBSTR(TO_CHAR(start_date,'DD-MON-YY'),5,1) = 'O') THEN
RAISE jack_frost;
END IF;
EXCEPTION
WHEN jack_frost THEN
RAISE_APPLICATION_ERROR(-20101, 'my god its freezing jackass');
END;

What I get is: Warning: Trigger created with compilation errors.

For help in clarity what I have done with the SUBSTR function is:
I used a select statement to test the syntax of my conditional testing was correct
select *
from voyage
where SUBSTR(TO_CHAR(start_date,'DD-MON-YY'),5,1) = 'E';

This returns all the Months with an 'E' as the fifth single date character in them. E.G. FEB and SEP. Replace the 'E' with an 'O' and this will cover 'NOV' exclusively.

But I still get the warning and it stops all inserts with the error.

Any help as I think it may be a syntax but my lecture is quite frankly useless and unless it is written in his papers he can't help.

any tips

Cheers
Re: Trigger problems [message #232629 is a reply to message #232628] Sun, 22 April 2007 13:04 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>What I get is: Warning: Trigger created with compilation errors.
Error? What error? I don't see any error.
Please read & follow the #1 Sticky post at the top of this forum.
Please use CUT & PASTE to show us complete interaction with Oracle.
After getting warning, please do the following:
SQL> SHOW ERROR
Re: Trigger problems [message #232631 is a reply to message #232628] Sun, 22 April 2007 13:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is this crap with substr on a month name.
Use extract function.

Regards
Michel
Re: Trigger problems [message #232632 is a reply to message #232628] Sun, 22 April 2007 13:24 Go to previous messageGo to next message
combitz
Messages: 2
Registered: April 2007
Location: UK
Junior Member
Thanks for the tip with the SHOW ERROR. We've not been shown that at all and its not in any note that I have access to but it does reveal the error

4/10 PLS-00049: bad bind variable 'NEW.SUBSTR'

Quote:
What is this crap with substr on a month name.
Use extract function.


Its called using my initiative to solve a problem. We have not been shown anything like this and every time I ask the tutor anything he clams up unless he can see it in his notes.I'm not trying to make excuses but I'm finding it difficult to learn without any support. I'm looking up extract function now.

Thanks for the help
Re: Trigger problems [message #232636 is a reply to message #232628] Sun, 22 April 2007 13:38 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
> We've not been shown that at all and its not in any note that I have access to but it does reveal the error
You'd might be surprised what you have access to.
There is online HELP.
SQL> HELP
When all else fails you could simply Read The Fine Manual on SQL*Plus found at http://tahiti.oracle.com along with the whole Doc. set
Re: Trigger problems [message #232637 is a reply to message #232632] Sun, 22 April 2007 13:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
All Oracle documentation are on line, above all SQL Reference.
This will give you the extract function (see link above) and examples of how to write a trigger (then you'll find your error).
You also have PL/SQL User's Guide and Reference and Application Developer's Guide - Fundamentals.

Regards
Michel

Re: Trigger problems [message #232732 is a reply to message #232637] Mon, 23 April 2007 02:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
9.5/10 for lateral thinking on the date problem, but there are better ways of doing it.
If you look here you will see a list of the formats that you can use. If I were looking to see if the month was November, I'd just try to extract the month in the form of a number from 1-12 and check to what value that was. That way you avoid a whole raft of problems with different NLS (National Language Support) settings.

The problem you are getting with :NEW.SUBSTR... is this:

The :OLD and :NEW only apply to columns in the table, so you could say
SUBSTR(:NEW.column_name,1,10)
, but not
:NEW.SUBSTR(column_name,1,10)
Re: Trigger problems [message #232851 is a reply to message #232628] Mon, 23 April 2007 10:57 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
CREATE OR REPLACE TRIGGER VOYAGE_T1
BEFORE INSERT ON voyage
FOR EACH ROW
BEGIN
IF TO_CHAR(:new.start_date,'MM') = '11' AND :NEW.DURATION = 5 THEN
  RAISE_APPLICATION_ERROR(-20101, 'my god its freezing jackass');
END IF;
END;


[Updated on: Mon, 23 April 2007 10:59]

Report message to a moderator

Previous Topic: interchage sql
Next Topic: Oracle Trace (Debug) Files
Goto Forum:
  


Current Time: Wed Dec 07 18:15:40 CST 2016

Total time taken to generate the page: 0.15517 seconds