Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Help
Trigger Help [message #399958] Fri, 24 April 2009 12:16 Go to next message
vortex566
Messages: 2
Registered: April 2009
Junior Member
I have the following code....

SHOW ERRORS;

CREATE OR REPLACE TRIGGER libloan_zero
BEFORE INSERT OR UPDATE ON bookloan
FOR EACH ROW
DECLARE

thisfirstname VARCHAR2(25);
thislastname VARCHAR2(25);
thisbook_title VARCHAR2(25);
thislocation VARCHAR2(25);
 
BEGIN
 
IF :NEW.evaluation =0 THEN
          SELECT customer.firstname,customer.surname,book.title,bookcopy.location
          INTO thisfirstname, thissurname, thisbook_title, thislocation
          FROM customer, bookcopy, book
          WHERE customer.customer_id = :new.customer_id
AND bookcopy.copy_id = :new.copy_id
AND book.book_id = copy.book_id;
 
INSERT INTO evaluation VALUES (thisfirstname,thislastname,thistitle,:NEW.hire_date, thislocation, :NEW.evaluation);
END IF;
END;


I get the following when trying to run it
7/1 PLS-00103: Encountered the symbol " " when expecting one of the f ollowing: begin function package pragma procedure subtype typ e use <an identifier> <a double-quoted delimited-identifier> f orm current cursor

Any ideas as to why?
Re: Trigger Help [message #399961 is a reply to message #399958] Fri, 24 April 2009 12:35 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Do a cut and paste of you session so we can see where line 7 is. and you do SHOW ERRORS after you create your code, not before.
Re: Trigger Help [message #399971 is a reply to message #399958] Fri, 24 April 2009 13:19 Go to previous messageGo to next message
vortex566
Messages: 2
Registered: April 2009
Junior Member
I have solved this one now but the following trigger which was working fine before is now producing an error.

SHOW ERRORS;
CREATE OR REPLACE TRIGGER libloan_december 
BEFORE INSERT OR UPDATE ON bookloan 
FOR EACH ROW
DECLARE
out_of_range EXCEPTION; 
thislocation VARCHAR2(20);
BEGIN
SELECT location INTO thislocation
FROM bookcopy WHERE copy_id = :new.copy_id;
IF thislocation = 'London' THEN 
IF TO_CHAR(:new.hire_date , 'MONTH') = 'DECEMBER ' 
THEN RAISE out _of_range; 
END IF; 
END IF; 
EXCEPTION 
WHEN out_of_range THEN RAISE_APPLICATION_ ERROR(-20100, 'No books can be hired in London during December'); end;


9/16 PLS-00103: Encountered the symbol "_" when expecting one of the f ollowing: . ; The symbol ". was inserted before "_" to contin ue.
13/43 PLS-00103: Encountered the symbol "ERROR" when expecting one of t he following: := . ( @ % ; The symbol ":=" was substituted fo r "ERROR" to continue.
Re: Trigger Help [message #399972 is a reply to message #399958] Fri, 24 April 2009 13:27 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
If it says:

Encountered the symbol "ERROR"

I'd be looking for ERROR in the code to see if it looks right.

cause this:
RAISE_APPLICATION_ ERROR

doesn't.
Re: Trigger Help [message #399977 is a reply to message #399971] Fri, 24 April 2009 13:52 Go to previous message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this the way you write your code?
See the mess it is.
It does not surprise me your have bugs and you can't see them.
Knows how to indent and write a readable code.

Regards
Michel
Previous Topic: PLS-00382: expression is of wrong type
Next Topic: Pls help to write SQL
Goto Forum:
  


Current Time: Mon Dec 05 02:45:16 CST 2016

Total time taken to generate the page: 0.05084 seconds