Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Student: Simple PL/SQL Question

Re: Student: Simple PL/SQL Question

From: Artem Duvanov <artem_duvanov_at_mail.ru>
Date: Sun, 29 Apr 2001 03:20:55 +0400
Message-ID: <9cfja0$sp9$1@mail.comset.net>

I've found two mistakes in the trigger.
1. You can't use rollback and commit in triggers. 2. Don't expect that any operators after raise_application_error will be executed. Execution of the trigger will IMMIDIATELY end after execution of raise_application_error, and rollback will be issued automatically. I would suggest you to write in the end of your trigger the following lines:

IF v_validate_result > 0
THEN RAISE_APPLICATION_ERROR (-20001, 'Please Pay Outstanding Dues'); END IF;

It's enough.
Artem

student <kennylim_at_techie.com> wrote in message news:9cf0ct$8lm$1_at_nntp9.atl.mindspring.net...
>
> Hi guys,
>
> I am just a student experimenting on PL/SQL and was
> wondering if someone will be kind enough to help
> shed some light in getting past this error message.
> This is just a base logic that i am still constructing to
> test on the movies schema. So please bare with me
> if I am missing logics and exception prior to completion.
>
> Thanks and have a nice day guys.
>
> Task:
> -------
>
> 1. Execute p_movierentalupdate to insert new records into "movierental"
 and
> "rentalitem" table.
> 2. Upon insert, update or delete, trigger t_movierentalcheck will be
 invoked
> to call p_movierentalcheck
> 3. Procedure p_movierentalcheck will then check for outstanding dues based
> on the given customerid
> and provide exception if there is balance due else, prompt successful
> completion of transaction.
>
> Problem:
> ------------
>
> When executing or debugging p_movierentalupdate, I received the following
> error while stepping through the end of the routine.
>
> I have no problem executing and getting the proc to work on both
> p_movierentalupdate and p_movierentalcheck
> "separately" if I disable t_movierentalcheck trigger.
>
> 1 8 7 ORA-01403: no data found
> ORA-01403: no data found
> ORA-06512: at "MOVIES.P_MOVIERENTALCHECK", line 8
> ORA-06512: at "MOVIES.T_MOVIERENTALCHECK", line 7
> ORA-04088: error during execution of trigger 'MOVIES.T_MOVIERENTALCHECK'
> ORA-06512: at "MOVIES.MOVIERENTALUPDATE", line 14
> ORA-06512: at line 9
>
>



> =====
>
> --Procedure to update records
>
> CREATE OR REPLACE PROCEDURE MOVIES.Rental_Update
> (
> v_rentaldate movierental.rentaldate%TYPE := SYSDATE,
> v_duedate movierental.duedate%TYPE := SYSDATE+3,
> v_totalcharge movierental.totalcharge%TYPE,
> v_customerid movierental.customerid%TYPE,
> v_employeeid movierental.employeeid%TYPE,
> v_itemnumber rentalitem.itemnumber%TYPE,
> v_moviecopyid rentalitem.moviecopyid%TYPE
> )
> AS
> BEGIN
>
> INSERT INTO movierental VALUES
> (rentalidseq.NEXTVAL,v_rentaldate, v_duedate, v_totalcharge, v_customerid,
> v_employeeid, NULL);
>
> INSERT INTO rentalitem VALUES
> (rentalidseq.CURRVAL, v_itemnumber, v_moviecopyid);
>
> COMMIT;
>
> DBMS_OUTPUT.PUT_LINE('MOVIE TRANSACTION HAD BEEN SUCCESSFULLY UPDATED!');
> END;
> /
>
> --Procedure to check for late return
>
> CREATE OR REPLACE PROCEDURE MOVIES.P_MOVIERENTALCHECK
> (v_customerid IN number, v_validate_result OUT number)
>
> AS
> BEGIN
>
>
> SELECT SUM(ROUND(returndate-duedate)*totalcharge)
> INTO v_validate_result
> FROM movierental
> WHERE returndate > duedate
> AND customerid = v_customerid
> GROUP BY customerid;
>
> -- debug message
> dbms_output.put_line(v_Validate_Result);
>
> END ;
> /
>
> --Trigger to call
>
> CREATE OR REPLACE TRIGGER MOVIES.T_MOVIERENTALCHECK
> BEFORE INSERT OR UPDATE OF RETURNDATE OR DELETE
> ON MOVIES.MOVIERENTAL
> REFERENCING OLD AS OLD NEW AS NEW
> FOR EACH ROW
> declare
> v_customer_id number;
> v_validate_result number;
>
> BEGIN
> v_customer_id := :old.customerid;
> p_movierentalcheck (v_customer_id, v_validate_result);
>
> IF v_validate_result > 0
> THEN RAISE_APPLICATION_ERROR (-20001, 'Please Pay Outstanding Dues');
> ROLLBACK;
> ELSE
> COMMIT;
> END IF;
>
> END;
>
> /
>
>
>
Received on Sat Apr 28 2001 - 18:20:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US