Skip navigation

DEFAULT ON UPDATE - column value for updates

score 230
You have not voted. Active

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.

Comments

Vote history