I can't put a select query in the if statement. How should I replace it?

Question

CREATE OR REPLACE TRIGGER my_trigger
  BEFORE INSERT ON my_table
  FOR EACH ROW
BEGIN
  IF :new.year_production < (select extract(year from sysdate) from dual)  OR :new.year_production< (1817) THEN
    RAISE_APPLICATION_ERROR (-20000, 'ERROR!');
  END IF;
END;

I can't put a select query in the if statement. How should I replace it?

Answer 1

You don't need a subquery. Just:

CREATE OR REPLACE TRIGGER my_trigger
  BEFORE INSERT ON my_table
  FOR EACH ROW
BEGIN
  IF :new.year_production < extract(year from sysdate) OR :new.year_production < 1817 THEN
    RAISE_APPLICATION_ERROR (-20000, 'ERROR!');
  END IF;
END;

Note that the second condition, that checks the year against 1817 is just unnecessary... unless you are planning to play around with your system date and set it to something earlier than year 1817!