Trigger for only changed values
Solution 1
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;
Solution 2
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;
Jaanna
Updated on March 20, 2021Comments
-
Jaanna over 3 years
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.
-
Alex Poole about 12 years+1, but 9i docs? <grin> Here's the 11g version
-
Bernhard Döbler almost 9 yearsThis seems to trigger if th yolumn is part of the UPDATE statement. Not only if the value changes.
-
Alieh S almost 4 yearsThis solution does not work with NULL columns. Comparison operators with NULL always return FALSE.
-
Maxim Mandrik almost 4 years@Oleg Thanks for the very helpful comment. Updated answer.
-
mustaccio over 3 yearsOracle considers empty strings (
''
) to be null values, sonvl(a_character_expression, '')
is meaningless. -
Yabasa about 3 yearsThis is the simplest and most concise answer for handling null comparison although see answer from @MaximMandrik for the full logic needed in the trigger.