How to successfully reference another table before insert with a trigger

I’m trying to create a trigger to validate if a new entry in the table registraties (registrations) contains a valid MNR (employee number) but I’m getting stuck on the part where I’m referencing the table medewerkers (employees).

Could someone help me out?

CREATE OR REPLACE TRIGGER t_MNRcontrole

BEFORE INSERT OR UPDATE 
ON registraties

DECLARE 
MNR_medewerkers number (SELECT MNR FROM MEDEWERKERS);

FOR EACH ROW
BEGIN 

IF :new.MNR <> MNR_medewerkers
THEN raise_application_error(-20111, 'Medewerker niet herkend!');

END IF;
END;

Error message received is
ORA-24344: success with compilation error

Answer

The PL/SQL assignment operator is :=, or select x into y from z to populate from a SQL query.

FOR EACH ROW is part of the trigger spec, not the PL/SQL code.

If :new.mnr is not present in the parent table, you will get a no_data_found exception, not a mismatched variable.

It’s good practice for error messages to include details of what failed.

In programming, we use indentation to indicate code structure.

A fixed version would be something like:

create or replace trigger trg_mnrcontrole
    before insert or update on registraties
    for each row
declare
    mnr_medewerkers medewerkers.mnr%type;
begin
    select mw.mnr into mnr_medewerkers
    from   medewerkers mw
    where  mw.mnr = :new.mnr;

exception  
    when no_data_found then
        raise_application_error(-20111, 'Medewerker '||:new.mnr||' niet herkend!');
end;

However, we can implement this kind of check better using a foreign key constraint, for example:

alter table registraties add constraint registraties_mw_fk
foreign key (mnr) references medewerkers.mnr;