Trigger for only changed values

Question

Say we have 3 records in table: orig_tab

---------------------------------------------
|  PK  |  Name  |  Address  |  Postal Code  |
---------------------------------------------
|   1  |  AA    |  Street1  |   11111       |
|   2  |  BB    |  Street2  |   22222       |
|   3  |  CC    |  Street3  |   33333       |
---------------------------------------------

Now the data is changed:

---------------------------------------------
|  PK  |  Name  |  Address  |  Postal Code  |
---------------------------------------------
|   1  |  AA    |  Street1  |   11111       |
|   2  |  BB    |  Street2  |   44444       |
|   3  |  CC    |  Dtreet7  |   33333       |
---------------------------------------------

What client wants is the update records and only the updated columns (yes, I know it doesn't make any sense but they use some old system from 1970s and they want to do some logging etc.). So the reporting table should be like this:

---------------------------------------------
|  PK  |  Name  |  Address  |  Postal Code  |
---------------------------------------------
|   2  |        |           |   44444       |
|   3  |        |  Dtreet7  |               |
---------------------------------------------

This what I tried:

CREATE OR REPLACE TRIGGER vr_reporting_trigger
   AFTER UPDATE ON orig_tab
   FOR EACH ROW
BEGIN
IF inserting THEN
INSERT INTO rep_tab(pk, name, address, code)
  SELECT :new.pk, :new.name, :new.address, :new,code FROM DUAL
  WHERE NOT EXISTS (SELECT 1 FROM rep_tab WHERE pk = :new.pk);
UPDATE rep_tab t SET t.name = :new.name, t.address = :new.address, t.code = :new.code 
   WHERE t.pk = :new.pk;
ELSIF updating THEN
IF :new.pk <> :old.pk THEN
     UPDATE rep_tab t
        SET t.name = :new.name, t.address = :new.address, t.code =: new.code
      WHERE t.pk = :old.pk ;
  END IF;
  MERGE INTO rep_tab d
  USING DUAL ON (d.pk = :old.pk)
  WHEN MATCHED THEN
  UPDATE SET d.name = :new.name, d.address = :new.address, d.code =: new.code
  WHEN NOT MATCHED THEN
  INSERT (d.pk,d.name, d.address, d.code) VALUES (:new.pk,:new.name, new.address, new.code);
END IF;
END;

with this solution, I get:

---------------------------------------------
|  PK  |  Name  |  Address  |  Postal Code  |
---------------------------------------------
|   2  |  BB    |  Street2  |   44444       |
|   3  |  CC    |  Dtreet7  |   33333       |
---------------------------------------------

I know that it somewhere in insert claus in when updating statement but I can't figure out how to have this claus changed as per my requirement. Any suggestion?

Thanks in advance.

Florin Ghita · Accepted Answer · 2015-09-07T11:53:53.663

29

You need this:

In an UPDATE trigger, a column name can be specified with an UPDATING conditional predicate to determine if the named column is being updated. For example, assume a trigger is defined as the following:

CREATE OR REPLACE TRIGGER ...
... UPDATE OF Sal, Comm ON Emp_tab ...
BEGIN

... IF UPDATING ('SAL') THEN ... END IF;

END;

From Oracle documentation(9i)

11gR2 documentation

edited Sep 07 '15 at 11:53

answered Jun 26 '12 at 05:32

Florin Ghita

17,525
6
57
76

4

+1, but 9i docs? Here's the [11g version](http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#sthref771) – Alex Poole Jun 26 '12 at 08:37
7

This seems to trigger if th yolumn is part of the UPDATE statement. Not only if the value changes. – Bernhard Döbler Aug 10 '15 at 18:30

Maxim Mandrik · Answer 2 · 2020-09-07T06:36:58.193

Trigger for only changed values.

As an example (only NOT NULL support) (comparison operators with NULL always return FALSE):

CREATE OR REPLACE TRIGGER trigger_department_update
  BEFORE UPDATE OF 
      department_id,
      cluster_id, 
      division_id, 
      macroregion_id, 
      address, 
      email
    ON cl_department
  FOR EACH ROW
BEGIN
  IF (
    :new.department_id != :old.department_id
    OR :new.cluster_id != :old.cluster_id
    OR :new.division_id != :old.division_id
    OR :new.macroregion_id != :old.macroregion_id
    OR :new.address != :old.address
    OR :new.email != :old.email
  ) 
    THEN :new.update_date := CURRENT_TIMESTAMP; 
  END IF;
END;

With NULL support (Thanks to @Oleg in the comments below this post) (Thanks to @Chris Bandy for the answer):

CREATE OR REPLACE TRIGGER trigger_department_update
  BEFORE UPDATE OF 
      department_id,
      cluster_id, 
      division_id, 
      macroregion_id, 
      address, 
      email
    ON cl_department
  FOR EACH ROW
BEGIN
  IF (
    ((:new.department_id <> :old.department_id OR :new.department_id IS NULL OR :old.department_id IS NULL) AND NOT (:new.department_id IS NULL AND :old.department_id IS NULL))
    OR ((:new.cluster_id <> :old.cluster_id OR :new.cluster_id IS NULL OR :old.cluster_id IS NULL) AND NOT (:new.cluster_id IS NULL AND :old.cluster_id IS NULL))
    OR ((:new.division_id <> :old.division_id OR :new.division_id IS NULL OR :old.division_id IS NULL) AND NOT (:new.division_id IS NULL AND :old.division_id IS NULL))
    OR ((:new.macroregion_id <> :old.macroregion_id OR :new.macroregion_id IS NULL OR :old.macroregion_id IS NULL) AND NOT (:new.macroregion_id IS NULL AND :old.macroregion_id IS NULL))
    OR ((:new.address <> :old.address OR :new.address IS NULL OR :old.address IS NULL) AND NOT (:new.address IS NULL AND :old.address IS NULL))
    OR ((:new.email <> :old.email OR :new.email IS NULL OR :old.email IS NULL) AND NOT (:new.email IS NULL AND :old.email IS NULL))
  ) 
    THEN :new.update_date := CURRENT_TIMESTAMP; 
  END IF;
END;

This solution does not work with NULL columns. Comparison operators with NULL always return FALSE. — Alieh S, Aug 24 '20 at 16:21

score 0 · Answer 3 · edited Mar 19 '21 at 19:27

0

to treat null :

... nvl(to_char(:new.department_id,' ') <> nvl(to_char(:old.department_id,' ') OR ...

edited Mar 19 '21 at 19:27

mustaccio

18,234
16
48
57

answered Mar 16 '21 at 21:57

wintersocram

97
2

Oracle considers empty strings (`''`) to be null values, so `nvl(a_character_expression, '')` is meaningless. – mustaccio Mar 19 '21 at 19:27
This is the simplest and most concise answer for handling null comparison although see answer from @MaximMandrik for the full logic needed in the trigger. – Yabasa May 28 '21 at 15:45
It's missing a close parenthesis after the field name `nvl(to_char(:old.department_id),' ')` – pauloya Sep 26 '22 at 11:25

Trigger for only changed values

3 Answers3

Linked