Automatically updating a field after a manuall update in Oracle SQL

I have a table that has a bunch of columns, one of which is a ‘last_update_time’ column. Whenever I change the value of any of the other columns, I would like ‘last_update_time’ to automatically update to SYSDATE.

I know that you would usually use triggers for this kind of thing, but as far as I am aware it is not possible to have a trigger for a table mutate that same table.

What is the best way of implementing the functionallity described above?

Answer

You can use a trigger before update of. You only need to specify the columns that might be updated in the clause before of

Take a look at this

SQL> create table testtrg ( c1 number, c2 date ) ;

Table created.

SQL> create or replace trigger mytrig
before update of c1
on testtrg
referencing new as new old as old
for each row
declare
begin
   :new.c2  := sysdate;
end;
/  2    3    4    5    6    7    8    9   10   11   12   13

Trigger created.

SQL> select * from testtrg;

no rows selected

SQL> insert into testtrg values ( 1 , sysdate - 360 );

1 row created.

SQL> commit;

Commit complete.

SQL> select * from testtrg ;

        C1 C2
---------- ---------
         1 28-JUL-19

SQL> update testtrg set c1=2 ;

1 row updated.

SQL> commit ;

Commit complete.

SQL>  select * from testtrg ;

        C1 C2
---------- ---------
         2 22-JUL-20

SQL>