Re: Conditional Trigger

mowinom_at_hotmail.com wrote:
> Daniel and David, thanks.
>
> In deed, Daniel, they are four and not three as stated above, and I am
> sorry that I left some more details out when I wrote the first time.
> It's like this:
>
> For a new article to be added to the table with a ISBN that already
> exists in the in that field, it must exist in the PERIODICAL_OWNER
> field. The logic behind this is that instead of enforcing a UNIQUE
> constraint on ISBN a trigger is used to prevent unnecessary duplicates
> in ISBN. The reason for avoinding the unique constraint is that one or
> more articles may come in with the same ISBN in different periodicals.
> In this case, therefore, the PERIODICAL#_OWNER must be included and it
> refers to the the original PERIODICAL#.
>
> PERIODICAL# (NUMBER 9).
> ISBN (NUMBER 9),
> TITLE (VARCHAR2 500)
> PERIODICAL_OWNER (NUMBER 9)
>
> I did modify the trigger thus:
>
> CREATE OR REPLACE TRIGGER ibr_prdc
> BEFORE INSERT ON PERIODICAL FOR EACH ROW
> DECLARE
> ISBNo PERIODICAL.ISBN%TYPE;
> tnr PERIODICAL.PERIODICAL#%TYPE;
> tnre PERIODICAL.PERIODICAL#_OWNER%TYPE;
> BEGIN
> Select ISBN, PERIODICAL#,PERIODICAL#_OWNER into ISBNo,tnr,tnre
> FROM PERIODICAL Where ISBNo =:New.ISBN And tnre = :New.PERIODICAL#;
> IF ISBNo = :New.ISBN And
> tnre <>:New.PERIODICAL# THEN
> RAISE_APPLICATION_ERROR(-20070, 'ERROR>>' ||
> ' PERIODICAL:' ||
> ' For ISBN that already exists in the basen, it' ||
> ' must exist in periodicald to be accepted.');
> ELSE
> :New.ISBN := :New.ISBN;
> END IF;
>
> Daniel, when I use BEFORE INSERT I get:
> ORA-01403: no data was found
> ...
>
> When I use AFTER INSERT I get:
> ORA-04091: The table PERIODICAL is muteting, ...
>
> The trigger is not reporting any errors when compiled.
> ..
>
> Thank you guys for the help:)
>
> --
> Me

There is nothing wrong with generating exceptions if you code appropriately to handle them. Look here: http://www.psoug.org/reference/exception_handling.html specifically at the use of PRAGMA EXCEPTION_INIT.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org