CODEWITHSUNDEEP
oracle-databaseplsqldatabase-trigger
Pardeep Kumar
Pardeep Kumar

Reputation: 19

Trigger to insert data row into new table when old table is updated

Want to insert only the updated row into the log table, but got error for the following trigger block : 

CREATE OR REPLACE TRIGGER system_notification_audit
  AFTER INSERT OR UPDATE on ncr_system_notification
  FOR EACH ROW
BEGIN
  insert into ncr_system_notification_log
  values
    (:NEW.ID,
     :NEW.NAME,
     :NEW.Description,
     :NEW.PREFERENCE,
     :NEW.FREQUENCY,
     :NEW.IS_HIGH,
     :NEW.IS_REQUIRED,
     :NEW.UPDATED_BY,
     :NEW.UPDATED_DATE) 
   where :OLD.IS_REQUIRED <> :NEW.IS_REQUIRED;
END;

Upvotes: 0

Views: 117

Answers (2)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65228

Use an insert with a select statement :

insert into ncr_system_notification_log 
select :NEW.ID , :NEW.NAME, :NEW.Description, :NEW.PREFERENCE, :NEW.FREQUENCY, 
       :NEW.IS_HIGH, :NEW.IS_REQUIRED, :NEW.UPDATED_BY, :NEW.UPDATED_DATE
  from dual
 where :OLD.IS_REQUIRED <> :NEW.IS_REQUIRED;

Assume IS_REQUIRED a nullable non-negative integer column, then a negative value for nvl function's second argument might be assumed a negative integer such as -1 in the inequality as :

where nvl(:OLD.IS_REQUIRED,-1) <> nvl(:NEW.IS_REQUIRED,-1)

Upvotes: 0

APC
APC

Reputation: 146239

The standard way to do this is with a PL/SQL conditional:

CREATE OR REPLACE TRIGGER system_notification_audit
 AFTER 
      INSERT OR 
      UPDATE 
on ncr_system_notification
FOR EACH ROW
begin

    if :OLD.IS_REQUIRED <> :NEW.IS_REQUIRED then
        insert into ncr_system_notification_log
        VALUES (:NEW.ID , :NEW.NAME, :NEW.Description, :NEW.PREFERENCE, :NEW.FREQUENCY, :NEW.IS_HIGH, :NEW.IS_REQUIRED, :NEW.UPDATED_BY, :NEW.UPDATED_DATE) ;     
    end if ;
END;

Upvotes: 1

Related Questions