CODEWITHSUNDEEP
oracle-databaseplsqltriggers
Mateusz Jamrocki
Mateusz Jamrocki

Reputation: 11

SQL trigger - nesting INSERT INTO in IF condition

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.

Upvotes: 1

Views: 112

Answers (1)

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

Reputation: 50017

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.

Upvotes: 2

Related Questions