trigger execution against condition satisfaction

Question

I have created this trigger which should give a error, whenever the value of new rctmemenrolno of table-receipts1 is matched with the memenrolno of table- memmast, but it is giving error in both condition(it is matched or not matched). kindly help me.

CREATE OR REPLACE TRIGGER HDD_CABLE.trg_rctenrolno
before insert ON HDD_CABLE.RECEIPTS1 for each row
declare
v_enrolno varchar2(9);
cursor c1 is select memenrolno from memmast;
begin
open c1;
fetch c1 into v_enrolno;
LOOP
    If :new.rctmemenrolno<>v_enrolno
then
raise_application_error(-20186,'PLEASE ENTER CORRECT ENROLLMENT NO');
close c1;
end if;
END LOOP;
end;

Answer 1

It looks like you're looping through all possible values of memenrolno and making sure the new value matches every possible value, which will always be false when there is more than one possible memenrolno.

Try this instead.

CREATE OR REPLACE TRIGGER HDD_CABLE.trg_rctenrolno
before insert ON HDD_CABLE.RECEIPTS1 for each row
begin
    If NOT EXISTS SELECT 1 FROM memast WHERE memenrolno = :new.rctmemenrolno
        then
            raise_application_error(-20186,'PLEASE ENTER CORRECT ENROLLMENT NO');
    end if;
end;

Answer 2

You are validating whether the entered RECEIPTS1.rctmemenrolno matches a memenrolno in MEMAST, right? Well a trigger is the wrong way to do this.

The loop completely won't scale (the more rows in MEMAST the longer it will take to insert a record in RECEIPTS1). But even a direct lookup to check for the existence of the specified key will still suck. Also this approach fails to work safely in a multi-user environment because Oracle uses the READ COMMITTED isolation level. In other words while your transaction is making the check some other session is deleting the row you have just found. Which results in a corrupt database.

The one and only correct way to do this is with a foreign key constraint.

alter table receipt1
    add constraint receipts1_memast_fk foreign key (rctmemenrolno)
                  references memast (memenrolno);  

Of course, this presumes you have a primary key - or a unique constraint - on memast to enforce the uniquess of memenrolno. I fervently hope you are not trying to enforce its uniqueness through triggers.

edit

"already I have 1 primary key as MEMID on memmast table"

If there is a one-to-one relationship between MEMID and MEMENROLNO, which is common when we have a business key and a surrogate key, then the proper solution is to use the primary key on RECEIPTS1. That is, drop the column RCTMEMENROL and replace it with RCTMEMID. Then plug those column names into that alter table statement. The front end would be responsible for providing the users with a facility to lookup the MEMID for a given MEMENROLNO, such as a List Of Values widget.

A yuckier solution would be to build a unique constraint on MEMAST.MEMENROLNO. That is not advisable because using natural keys to enforce foreign keys is a bad idea, and utterly mad when the table in question already has a synthetic primary key.

If there isn't a one-to-one relationship between MEMID and MEMENROLNO then I don't know what the purpose of the check is. A query on MEMAST can assert the existence of a given MEMENROLNO now but without a foreign key it can say nothing about the state of the database in five minutes time. So why bother with the check at all?