Re: Conditional Trigger

From: <mowinom_at_hotmail.com>
Date: Wed, 21 Nov 2007 14:21:27 -0800 (PST)
Message-ID: <e97dce95-e1e0-43e7-bdba-c56cea8f8c52@b15g2000hsa.googlegroups.com>

Hi all,

I've modified the trigger and used a cursor. It's working fairly well now. It'll accept a second ISBN in the ISBN field only if the value of PERIODICAL#_OWNER being entered exists in the ISBN column. What I've notice, however is that once entered, the PERIODICAL#_OWNER attribute accepts any value entered including nulls when updated. Any suggestion to better this is warmly welcome and appreciated.

David, you realize that this is not a school project. So, unconcealing the code would help, a lot.

Here the code:

CREATE OR REPLACE TRIGGER temp_test
BEFORE INSERT OR UPDATE OF ISBN ON PERIODICAL FOR EACH ROW WHEN (new.ISBN IS NOT NULL) DECLARE
   get VARCHAR2(9);
   invalid_value EXCEPTION;
   valid_value EXCEPTION;

  CURSOR curGet (issno VARCHAR2) IS
   Select ISBN FROM PERIODICAL

      Where  ISBN IN
	  	(SELECT ISBN FROM PERIODICAL
		  WHERE  PERIODICAL#  = :new.PERIODICAL#_OWNER)
         FOR UPDATE OF ISBN;

BEGIN    OPEN curGet (:new.ISBN);
   FETCH curGet INTO get;

   IF curGet%NOTFOUND THEN

      RAISE invalid_value;
  ELSE
      RAISE valid_value;
   END IF;
   CLOSE curGet;

EXCEPTION
   WHEN invalid_value THEN

      CLOSE curGet;
       RAISE_APPLICATION_ERROR(-20070, 'ERROR>>' ||


         ' PERIODICAL:' ||

         ' For ISBN that already exists in the basen, it' ||

         ' must exist in periodicald to be accepted.');

   WHEN valid_value THEN
      CLOSE curGet;

END; Received on Wed Nov 21 2007 - 16:21:27 CST