Oracle: automatic update change date

Question

I have a table in oracle and I want to examine updates in rows

id number, title varchar2(10), modify_date date 

I created a trigger to feed the change_date file:

 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 do a big update from another table, I would like update_date to be updated ONLY for rows with a new value, and not for all rows.

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

Is it possible? I thought that Oracle would not update the field with the same value

thanks

Answer 1

You mistakenly think that Oracle does what you order.

You can 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 a different 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;