CODEWITHSUNDEEP
sqloracle-databasetriggersdatabase-trigger
Big Green Alligator
Big Green Alligator

Reputation: 1185

Oracle SQL - ORA-04079: invalid trigger specification

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.

Upvotes: 0

Views: 1250

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270863

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.

Upvotes: 2

Related Questions