Oracle SQL trigger with JOIN

Question

CREATE OR REPLACE TRIGGER TRIGGER_NAME 
BEFORE INSERT OR UPDATE of ON TABLE_NAME 
FOR EACH ROW 
BEGIN
    if inserting then
        if :new.col_a < (select col_b from table_a natural join table_b where some_col = :new.some_value)
            then
            raise_application_error(-20000, 'Raise some error');
        end if;
    end if;
END;

when I try to compile I get this error

Error(6,93): PLS-00103: Encountered the symbol "JOIN" when expecting one of the following: ) , with group having intersect minus start union where connect The symbol "," was substituted for "JOIN" to continue.

What am I missing?

Littlefoot · Accepted Answer · 2021-05-26 11:00:08Z

1

It is not the JOIN that causes the error, but the fact that usage of a subquery is invalid in this context. Select value first (out of IF), use it later. Something like this:

CREATE OR REPLACE TRIGGER trigger_name
   BEFORE INSERT
   --OR UPDATE of    --> of what?
   ON table_name
   FOR EACH ROW
DECLARE
   l_col_b  table_b.col_b%TYPE;
BEGIN
   IF INSERTING
   THEN
      SELECT col_b
        INTO l_col_b
        FROM table_a NATURAL JOIN table_b
       WHERE some_col = :new.some_value;

      IF :new.col_a < l_col_b
      THEN
         raise_application_error (-20000, 'Raise some error');
      END IF;
   END IF;
END;

Also, use table aliases. It is impossible to know which column belongs to which table. Furthermore, your trigger wants to fire before update of ... what? I commented it out.

answered May 26, 2021 at 11:00

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL trigger with JOIN

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related