Trigger for only changed values
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
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;