With 12c we can now have a default value based upon things like a sequence or sysdate or a simple expression.
This is great because it can eliminate the need for an BEFORE ROW INSERT trigger.
Which in turn results in much better insert performance.
The same is needed for UPDATES!
Example what works for an INSERT
create sequence tmp_idea_insert_seq cache 10000;
create table tmp_idea_insert
( id number default tmp_idea_insert_seq.nextval not null
,name varchar2(100 byte)
,inserted_by varchar2(128) default coalesce(sys_context('userenv','client_identifier'),sys_context('userenv','session_user')) not null
,inserted_on date default sysdate not null
);
insert into tmp_idea_insert (name) values ('Test');
select * from tmp_idea_insert;
ID NAME INSERTED_BY INSERTED_ON
1 Test MYSELF 13.10.16 11:36:13
Of cause this is not restricted to an insert. However during an insert the old value is NULL therefore the default value kicks in.
Proposed syntax enhancement
create sequence tmp_idea_update_seq cache 10000;
create table tmp_idea_update
( id number default tmp_idea_insert_seq.nextval not null
,name varchar2(100 byte)
,inserted_by varchar2(128) default coalesce(sys_context('userenv','client_identifier'),sys_context('userenv','session_user')) not null
,inserted_on date default sysdate not null
,updated_by varchar2(128) default on update coalesce(sys_context('userenv','client_identifier'),sys_context('userenv','session_user'))
,updated_on date default on update sysdate
);
So that we can then do inserts and updates without the need for the typical AUDIT column triggers.
Syntax could also use the keyword ALWAYS instead of DEFAULT On UPDATE. This might be a bit more in line with the current logic.