Re: Conditional Trigger

From: <mowinom_at_hotmail.com>
Date: Tue, 20 Nov 2007 07:27:03 -0800 (PST)
Message-ID: <78e2142d-52de-422a-943f-6d20687f606e@l1g2000hsa.googlegroups.com>

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

Received on Tue Nov 20 2007 - 09:27:03 CST