SQL Trigger is not working for Audit

Question

Requirement: Once any update on column value in table OPS_BUILD_ACTIVITY_LABEL, we have to insert one row in ACTIVITY_LABEL_AUDIT_LOG table which is having columns as TABLE_NAME, COLUMN_NAME, OLD_VALUE, NEW_VALUE, UPDATED_BY, UPDATED_DTM

I have created Trigger as below. Trigger created with below compilation errors:

PL/SQL: SQL Statement ignored
PL/SQL: ORA-00984: column not allowed here

Please help me on this.

create or replace TRIGGER ACTIVITY_LABEL_TRIGGER
AFTER UPDATE OF ACTIVITY_LABEL, NOTES
ON OPS_BUILD_ACTIVITY_LABEL
FOR EACH ROW
BEGIN
  INSERT INTO ACTIVITY_LABEL_AUDIT_LOG
   ( TABLE_NAME,
     COLUMN_NAME,
     OLD_VALUE,
     NEW_VALUE,
     UPDATED_BY,
     UPDATED_DTM)   
     VALUES   

   ('OPS_BUILD_ACTIVITY_LABEL',
    'ACTIVITY_LABEL',
    OLD.ACTIVITY_LABEL,
    NEW.ACTIVITY_LABEL,
    NEW.LAST_UPDATED_BY,
    NEW.LAST_UPDATED_DTM); 

END;

Answer 1

Just Prefix OLD and NEW with colons(:) as in the following ( ORA-00984 raise because of this, by the way I assume there's no problem with column names ) :

CREATE OR REPLACE TRIGGER ACTIVITY_LABEL_TRIGGER
AFTER ON OPS_BUILD_ACTIVITY_LABEL
FOR EACH ROW
BEGIN
 if ( nvl(:OLD.ACTIVITY_LABEL,'xYz#@!') != nvl(:NEW.ACTIVITY_LABEL,'xYz#@!')
   or nvl(:OLD.NOTES,'xYz#@!') != nvl(:NEW.NOTES,'xYz#@!') ) then
  INSERT INTO ACTIVITY_LABEL_AUDIT_LOG
    (TABLE_NAME,
     COLUMN_NAME,
     OLD_VALUE,
     NEW_VALUE,
     UPDATED_BY,
     UPDATED_DTM)
  VALUES  
    ('OPS_BUILD_ACTIVITY_LABEL',
     'ACTIVITY_LABEL',
     :OLD.ACTIVITY_LABEL,
     :NEW.ACTIVITY_LABEL,
     :NEW.LAST_UPDATED_BY,
     :NEW.LAST_UPDATED_DTM);
 end if;
END;

With respect to your comment, I remove the part UPDATE OF ACTIVITY_LABEL, NOTES and make comparison for two columns ACTIVITY_LABEL and NOTES in the IF statement. If you want to get every log for each update operation, you should also remove the IF statement.

Answer 2

Thank you for all your answers, based on your answers finally I have achieved the requirement with the below code :

create or replace TRIGGER ACTIVITY_LABEL_TRIGGER
AFTER UPDATE OF ACTIVITY_LABEL, NOTES
ON OPS_BUILD_ACTIVITY_LABEL
FOR EACH ROW
BEGIN
 if ( nvl(:OLD.ACTIVITY_LABEL,'xYz#@!') != nvl(:NEW.ACTIVITY_LABEL,'xYz#@!')) then
  INSERT INTO ACTIVITY_LABEL_AUDIT_LOG
    (TABLE_NAME,
     COLUMN_NAME,
     OLD_VALUE,
     NEW_VALUE,
     UPDATED_BY,
     UPDATED_DTM)
  VALUES  
    ('OPS_BUILD_ACTIVITY_LABEL',
     'ACTIVITY_LABEL',
     :OLD.ACTIVITY_LABEL,
     :NEW.ACTIVITY_LABEL,
     :NEW.LAST_UPDATED_BY,
     :NEW.LAST_UPDATED_DTM);
 end if;

 if (nvl(:OLD.NOTES,'xYz#@!') != nvl(:NEW.NOTES,'xYz#@!') ) then
  INSERT INTO ACTIVITY_LABEL_AUDIT_LOG
    (TABLE_NAME,
     COLUMN_NAME,
     OLD_VALUE,
     NEW_VALUE,
     UPDATED_BY,
     UPDATED_DTM)
  VALUES  
    ('OPS_BUILD_ACTIVITY_LABEL',
     'NOTES',
     :OLD.NOTES,
     :NEW.NOTES,
     :NEW.LAST_UPDATED_BY,
     :NEW.LAST_UPDATED_DTM);
 end if;
END;