Trigger Question

I have the following trigger:

CREATE TRIGGER check_value
BEFORE
INSERT
ON y
FOR EACH ROW
DECLARE
   rec_in_prod NUMBER;
   rec_in_stage NUMBER;
BEGIN    SELECT count(*) INTO rec_in_prod FROM z WHERE F2 = :new.f2;    SELECT count(*) INTO rec_in_stage FROM x WHERE F2 = :new.f2;

   IF ((rec_in_prod = 0) AND

      (rec_in_stage = 0))
   THEN
     raise_application_error(-20000, 'invalid value for column F2.');    END IF; END check_value;

The trigger basically acts like a foreign key trigger, but the value for column F2 can be in either table X or table Z.

I get the following errors though:

SQL> insert into y values (5,5,5);
insert into y values (5,5,5)

            *
ERROR at line 1:

ORA-20000: invalid value for column F2.
ORA-06512: at line 10
ORA-04088: error during execution of trigger 'PTEST.CHECK_VALUE'

line 10 being the raise_application_error line. I cannot figure it out. I even tried to use the raise_application_error from the example in the book and got the same errors. Does anyone have any suggestions?

Thanks,
Heather Received on Fri Feb 28 1997 - 00:00:00 CET