SQL & PL/SQL

SQL> create table tbl(id number,dt date default sysdate)

  2  /

Table created.

SQL> insert into tbl(id) values(1)

  2  /

1 row created.

SQL> insert into tbl(id) values(2)

  2  /

1 row created.

SQL> select * from tbl

  2  /

        ID DT

---------- -------------------

         1 08/25/2017 13:23:24

         2 08/25/2017 13:23:32

SQL>

For updates you'll need a trigger.

SY.

That makes it non-transactional. As a result, each rows updated by a single UPDATE statement can have different update date. To make it transactional we should use package variable or better compound trigger:

SQL> create table tbl(

  2                  id number,

  3                  description varchar2(30),

  4                  date_created date default sysdate not null,

  5                  date_updated date,

  6                  constraint constraint_name primary key (id)

  7                  )

  8  /

Table created.

SQL> create or replace trigger trg_bu_tbl before update on tbl

  2    for each row

  3  begin

  4    :new.date_updated := sysdate;

  5    dbms_lock.sleep(2);

  6  end;

  7  /

Trigger created.

SQL> insert into tbl(id, description) values (1, 'Test1');

1 row created.

SQL> insert into tbl(id, description) values (2, 'Test2');

1 row created.

SQL> select * from tbl;

        ID DESCRIPTION                    DATE_CREATED        DATE_UPDATED

---------- ------------------------------ ------------------- -------------------

        1 Test1                          08/25/2017 15:34:38

        2 Test2                          08/25/2017 15:34:38

SQL> begin dbms_lock.sleep(10); end;

  2  /

PL/SQL procedure successfully completed.

SQL> update tbl

  2    set description = 'Test...' || id;

2 rows updated.

SQL> select * from tbl;

        ID DESCRIPTION                    DATE_CREATED        DATE_UPDATED

---------- ------------------------------ ------------------- -------------------

        1 Test...1                      08/25/2017 15:34:38 08/25/2017 15:34:48

        2 Test...2                      08/25/2017 15:34:38 08/25/2017 15:34:50

SQL> drop table tbl purge

  2  /

Table dropped.

SQL> create table tbl(

  2                  id number,

  3                  description varchar2(30),

  4                  date_created date default sysdate not null,

  5                  date_updated date,

  6                  constraint constraint_name primary key (id)

  7                  )

  8  /

Table created.

SQL> create or replace trigger trg_bu_tbl before update on tbl

  2    for each row

  3  begin

  4    :new.date_updated := sysdate;

  5    dbms_lock.sleep(2);

  6  end;

  7  /

Trigger created.

SQL> create or replace

  2    trigger trg_cmp_tbl

  3      for update

  4      on tbl

  5      compound trigger

  6      v_dt date := sysdate;

  7      before each row

  8        is

  9        begin

10            :new.date_updated := v_dt;

11            dbms_lock.sleep(2);

12        end before each row;

13  end;

14  /

Trigger created.

SQL> insert into tbl(id, description) values (1, 'Test1');

1 row created.

SQL> insert into tbl(id, description) values (2, 'Test2');

1 row created.

SQL> select * from tbl;

        ID DESCRIPTION                    DATE_CREATED        DATE_UPDATED

---------- ------------------------------ ------------------- -------------------

        1 Test1                          08/25/2017 15:35:47

        2 Test2                          08/25/2017 15:35:47

SQL> begin dbms_lock.sleep(10); end;

  2  /

PL/SQL procedure successfully completed.

SQL> update tbl

  2    set description = 'Test...' || id;

2 rows updated.

SQL> select * from tbl;

        ID DESCRIPTION                    DATE_CREATED        DATE_UPDATED

---------- ------------------------------ ------------------- -------------------

        1 Test...1                      08/25/2017 15:35:47 08/25/2017 15:35:59

        2 Test...2                      08/25/2017 15:35:47 08/25/2017 15:35:59

SQL>

SY.

Is there any way doing it without trigger ?

I doubted.

SY.

In many of our custom tables we use to have creation date and last update date columns. These columns are populated by insert statements and update statements.

I suggest you discuss eliminating those columns with your IT team. At least eliminate any 'last update' columns.

Knowing when a row was created might be marginally useful in some cases.

But the 'who' and the 'update' related columns are virtually useless and usually inaccurate for any real troubleshooting or auditing purposes.

The other issue is that those values only reflect row level changes. So if one user changes an important column and a later user changes ANYTHING - no matter how trivial - the second change will totally over-write/override the first change.

Meaning - you will NEVER know who changed that important column or when.

Want an example? Just use that code sample you marked 'correct'. It doesn't check to see if any column values really changed. It just checks if an update statement was executed.

So if I change an 'N' to an 'N' the value hasn't changed at all but the 'updated date' will change and hide the date that the value REALLY changed.

Which means I can hide the real change date of anything in the row just by updating the 'update date' column.

If you need to log 'who changed what and when' then log it. But don't muck up your tables by trying to log it in the table itself.