Oracle : Automatic modification date on update

Question

i have a table in oracle and i want to study the updates on lines

id number,
title varchar2(10),
modify_date date

i have created a trigger to feed the modify_date :

create or replace
TRIGGER schema.name_of_trigger
BEFORE UPDATE ON schema.name_of_table
FOR EACH ROW
BEGIN
    :new.modify_date := sysdate;
END;

but when i make a big update from another table i would like the modify_date to be updated ONLY for lines with a new value, not all lines.

update mytable a set title = (select title from mytable2 b where b.id = a.id)

Is it possible ? i thought Oracle would not update a field with the same value

Thanks

Answer 1

You thought wrongly, Oracle does what you order it to do.

You can either try

update mytable a set title = 
      (select title from mytable2 b 
        where b.id     = a.id and 
              b.title != a.title)

or change the trigger to specifically check for a different title name.

create or replace
TRIGGER schema.name_of_trigger
BEFORE UPDATE ON schema.name_of_table
FOR EACH ROW
BEGIN
--  Check for modification of title:
    if :new.title != :old.title then
       :new.modify_date := sysdate;
    end if;
END;

Answer 2

CREATE OR REPLACE TRIGGER TRG_AFT_UPD_MFD BEFORE UPDATE ON SUPER_TEST FOR EACH ROW
BEGIN
  IF :NEW.MODIFIED_ON != SYSDATE THEN
   :NEW.MODIFIED_ON := SYSDATE;
   DBMS_OUTPUT.PUT_LINE('UPDATE :'||:NEW.MODIFIED_ON);
  END IF;
END;