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.

Gordon Linoff · Accepted Answer · 2016-04-10 01:17:00Z

2

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.

answered Apr 10, 2016 at 1:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Oracle SQL - ORA-04079: invalid trigger specification

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