3

According to requirenments I need to support two mandatory columns for each table: ADDED_DATE, MODIFIED_DATE.
These are intended for DBA/auditing purposes.

Is it possible to make this stuff totally automatic, implicitly supported by DB itself when I'm inserting / updating records from within application?

Mike
  • 20,010
  • 25
  • 97
  • 140
  • possible duplicate of [How to find out when an Oracle table was updated the last time](http://stackoverflow.com/questions/265122/how-to-find-out-when-an-oracle-table-was-updated-the-last-time) – sleske Mar 25 '14 at 15:27

2 Answers2

4

create a trigger on the table (before update for each row).

SQL> create table foo (hi varchar2(10), added_date date, modified_date date);

Table created.

SQL> create trigger foo_auifer
  2  before update or insert on foo
  3  for each row
  4  declare
  5  begin
  6    if (inserting) then
  7      :new.added_date := sysdate;
  8    elsif (updating) then
  9      :new.modified_date := sysdate;
 10    end if;
 11  end;
 12  /

Trigger created.

SQL> insert into foo (hi) values ('TEST');

1 row created.

SQL> insert into foo (hi) values ('TEST2');

1 row created.

SQL> update foo set hi = 'MODDED' where rownum  = 1;

1 row updated.

SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

Session altered.

SQL> select * from foo;

HI         ADDED_DATE           MODIFIED_DATE
---------- -------------------- --------------------
MODDED     07-nov-2012 15:28:28 07-nov-2012 15:28:39
TEST2      07-nov-2012 15:28:30

SQL>
DazzaL
  • 21,638
  • 3
  • 49
  • 57
4
create table "db_user"."my_table" (
    ...
    "added_date" date default sysdate,
    "modified_date" date default sysdate
)
/
create or replace
trigger "db_user"."trg_my_table_audit" 
before update on my_table for each row 
begin 
    :new.modified_date := sysdate;
end;
Mike
  • 20,010
  • 25
  • 97
  • 140
  • +1 for pointing out that trigger is not needed for inserts. "default sysdate" works fine for inserts. You only need the trigger for updates. – Mike Brennan Feb 14 '14 at 23:45