CODEWITHSUNDEEP
oracle-databaseplsql
HungryProgrammer
HungryProgrammer

Reputation: 165

Oracle Trigger not working

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

Upvotes: 2

Views: 864

Answers (3)

David Aldridge
David Aldridge

Reputation: 52346

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

Maybe a NOT NULL constraint as well

Upvotes: 4

Datajam
Datajam

Reputation: 4231

Simple: you should have AND instead of OR. Your current code will raise an exception no matter the value of FLAG. Better yet, use NOT IN instead:

IF :new.flag NOT IN ('Y', 'N') THEN
    RAISE_APPLICATION_ERROR(-20100, 'Please insert Y  or N ');
END IF;

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132580

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'));

Upvotes: 4

Related Questions