Oracle SQL - ORA-04079: invalid trigger specification

Question

I have an EMPLOYEE table, which I want to create a trigger to log for when the employee commissions change (EMPCOMM). I have created an EMPLOYEE_COMM_AUDIT table to handle this. I have come up with the following code:

CREATE OR REPLACE TRIGGER EMPLOYEE_COMM_AUDIT_TRIGGER 
BEFORE DELETE OR INSERT OR UPDATE OF EMP_COMM ON EMPLOYEE 
IF (NEW.EMP_COMM != OLD.EMPCOMM)
BEGIN
  UPDATE EMPLOYEE_COMM_AUDIT
  SET EMPLOYEE_COMM_AUDIT.EMP_NUM = EMPLOYEE.EMP_NUM;
  SET EMPLOYEE_COMM_AUDIT.CHANGE_DATE = (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') FROM DUAL;
  SET EMPLOYEE_COMM_AUDIT.USERNAME = CURRENT_USER;
  SET EMPLOYEE_COMM_AUDIT.ACTION = NULL;
  SET EMPLOYEE_COMM_AUDIT.OLD_COMM = OLD.EMPCOMM;
  SET EMPLOYEE_COMM_ADUDIT.NEW_COMM = NEW.COMM;
  DBMS_OUTPUT_LINE("Employee Commisions Audit has been updated);
END;

However Oracle SQL tells me: ORA-04079: invalid trigger specification, but I'm not getting any red underlines anywhere to indicate where the fault is.

Can somebody please help me out? I have tried to have a look on these forums, but I can't seem to find a solid reply anywhere.

Thanks in advance.

score 2 · Accepted Answer · answered Apr 10 '16 at 01:17

Your UPDATE syntax is all wrong:

UPDATE EMPLOYEE_COMM_AUDIT
  SET CHANGE_DATE = SYSDATE,
      USERNAME = CURRENT_USER,
      ACTION = NULL,
      OLD_COMM = OLD.EMPCOMM,
      NEW_COMM = NEW.COMM
  WHERE EMP_NUM = :NEW.EMPNUM;

Changes and assumptions:

SET only appears once.
The table name does not need to be repeated.
The SET expressions are separated by commmas not semi-colons.
There is no need to convert sysdate to a date to assign it to a date column.
You need a WHERE clause to specify what row should be updated.

This fixes the update (or attempts to); there may be other issues.

Oracle SQL - ORA-04079: invalid trigger specification

1 Answers1