SQL trigger - nesting INSERT INTO in IF condition

Question

I want to create trigger registring in new table employees whom salary is being raised above 5000 and didn't get salary higher than 5000 so far.

Trigger I wrote is returning error Error(2,41): PL/SQL: ORA-00984: column not allowed here.

Here's my trigger:

CREATE OR REPLACE TRIGGER emp_gotrich_trig BEFORE UPDATE OF salary ON employees
FOR EACH ROW BEGIN
    IF :NEW.salary>5000 AND :OLD.salary<=5000 THEN
        INSERT INTO emp_gotrich VALUES (employee_id, SYSDATE, :OLD.salary, :NEW.salary);
    END IF;
END;

And here's emp_gotrich table:

CREATE TABLE emp_gotrich ( emp_id NUMBER(6), raise_date DATE, old_sal NUMBER(8,2), new_sal NUMBER(8,2) );

I guess that INSERT statemet isn't nested properly but i don't know what should i change.

I also tried to use 'WHEN' but i dont know where should i omitt colons, so it doeasn't work too.

CREATE OR REPLACE TRIGGER emp_getrich_log BEFORE UPDATE OF salary ON employees FOR EACH ROW
WHEN
    NEW.salary>5000 AND OLD.salary<=5000;
BEGIN
    INSERT INTO emp_gotrich VALUES(employee_id, SYSDATE, :OLD.salary, :NEW.salary);
END;

Please, help me find a way to run it.

Bob Jarvis - Слава Україні · Accepted Answer · 2017-01-18 22:49:32Z

2

You forgot to specify :OLD or :NEW on the employee_id value in your INSERT statement. I believe it should be:

INSERT INTO emp_gotrich
  (EMP_ID, RAISE_DATE, OLD_SAL, NEW_SAL)
VALUES
  (:OLD.employee_id, SYSDATE, :OLD.salary, :NEW.salary);

I suggest that a field list, such as the one I added, should always be included in an INSERT statement.

answered Jan 18, 2017 at 22:49

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

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

Collectives™ on Stack Overflow

SQL trigger - nesting INSERT INTO in IF condition

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