Oracle Trigger not working

Question

I need to develop a trigger which triggers when the value for a field is not 'Y' or 'N'. My code is below which is not working

CREATE OR REPLACE TRIGGER ONLY_Y_N
BEFORE INSERT OR UPDATE OF flag
ON checktable
FOR EACH ROW
BEGIN
IF :new.flag <>'Y' OR  :new.flag <>'N' THEN
RAISE_APPLICATION_ERROR(-20100, 'Please insert Y  or N ');
END IF;
END ONLY_Y_N;

Please help

Answer 1

As David Aldridge says, you want a check constraint not a trigger. However, the reason your trigger doesn't work is this condition:

IF :new.flag <>'Y' OR  :new.flag <>'N' THEN

Since 'Y' <> 'N' and 'N' <> 'Y' this will never be true! You need:

IF :new.flag <>'Y' AND  :new.flag <>'N' THEN

or more succinctly:

IF :new.flag not in ('Y', 'N') THEN

So the check constraint solution would be:

alter table checktable add constraint only_y_n check (flag in ('Y', 'N'));

Answer 2

The appropriate method for this would be a check constraint, not a trigger.

Maybe a NOT NULL constraint as well