question

stevensonbd2006 avatar image
stevensonbd2006 asked

Receiving errors firing triggers in Oracle

When attempting to fire trigger I receive this Error report: SQL Error: ORA-01722: invalid number ORA-06512: at "C##.AUDIT_CHANGES", line 10 ORA-04088: error during execution of trigger 'C##.AUDIT_CHANGES' 01722. 00000 - "invalid number" execute statement: UPDATE employee set salary = 68000 where employee_id=14; Trigger statement: create or replace TRIGGER AUDIT_CHANGES AFTER INSERT OR UPDATE OF "EMPLOYEE_ID","SALARY" ON EMPLOYEE REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW DECLARE v_change_by VARCHAR2 (50BYTE); v_timestamp TIMESTAMP (6); BEGIN IF INSERTING THEN INSERT INTO EMPLOYEE_AUDIT (OLD_EMPLOYEE_ID, NEW_EMPLOYEE_ID, OLD_SALARY, NEW_SALARY, CHANGE_TYPE, CHANGE_BY, TIMESTAMP) VALUES (NULL, :new.EMPLOYEE_ID, NULL, :new.SALARY, 'Inserting', v_change_by, v_timestamp); ELSE IF UPDATING THEN INSERT INTO EMPLOYEE_AUDIT (OLD_EMPLOYEE_ID, NEW_EMPLOYEE_ID, OLD_SALARY, NEW_SALARY, CHANGE_TYPE, CHANGE_BY, TIMESTAMP) VALUES (:old.EMPLOYEE_ID, :new.EMPLOYEE_ID, :old.SALARY, :new.SALARY, 'Updating', v_change_by, v_timestamp ); END IF; END IF; END;
oracletriggerplsql
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
Should the line reading **v\_change_by VARCHAR2 (50BYTE)** read **v\_change\_by VARCHAR2 (50)** instead? Also, the declared variables are never set, so what is their purpose?
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.