Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Student: Simple PL/SQL Question
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
>
>