Forum Stats

  • 3,824,838 Users
  • 2,260,429 Discussions
  • 7,896,325 Comments

Discussions

DEFAULT ON UPDATE - column value for updates

Sven W.
Sven W. Member Posts: 10,535 Gold Crown
edited Oct 13, 2016 5:48AM in Database Ideas - Ideas

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.

Sven W.Marco MischkeMathias ZarickWilliam RobertsonN BourgesPhilipp SalvisbergJorge Rimblasulohmann1244776User_JDNQ2Jeffrey Kempuser8937694AndyLuuAlbert Nelson AJohn_Kpnosko3808699Rainer Stenzel48f5a7f3-e768-49bd-a30e-c57a99479165Chase Marler3821423Lukas EderRichard Soule21dc3839-8b72-4090-b089-35cabc52daabJosh-OracleBilly Verreynnelead1111User_2KT88BartCernRostislav KushnirenkoJeff Kemp-Oracleandre.psantos
32 votes

Active · Last Updated

«1

Comments

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown
    edited Oct 13, 2016 9:10AM

    Your COALESCE() is missing SYS_CONTEXT('APEX$SESSION', 'APP_USER') and SYS_CONTEXT('PROGRAM_B', 'USER_ID') and SYS_CONTEXT( 'PROGRAM_C','username' ) and .... (ie the "user" information needs to be a function call)

    As far as AUDIT of Data Changes go... you need to upgrade to 12c or higher (so that you can record SYS_CONTEXT() settings also)

    Performance: A Fresh Look at Auditing Row Changes

    https://youtu.be/2RXUUrxcKrw?list=PLlo3zrPbMP9_HDjsq9lcdi8RyiIvqfmBy

    MK

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    Your COALESCE() is missing SYS_CONTEXT('APEX$SESSION', 'APP_USER') and SYS_CONTEXT('PROGRAM_B', 'USER_ID') and SYS_CONTEXT( 'PROGRAM_C','username' ) and .... (ie the "user" information needs to be a function call)

    As far as AUDIT of Data Changes go... you need to upgrade to 12c or higher (so that you can record SYS_CONTEXT() settings also)

    Performance: A Fresh Look at Auditing Row Changes

    https://youtu.be/2RXUUrxcKrw?list=PLlo3zrPbMP9_HDjsq9lcdi8RyiIvqfmBy

    MK

    Thanks for the upvote.

    I didn't add the APEX$SESSION context, because it could potentially confuse some of the DBAs out here. And it only works from apex 5 onwards.

    But that is how I nowadays write the insert "audit" columns. And with audit columns I'm just referring to the INSERTED_BY, UPDATED_BY columns that I see in so many projects. Not about writing/logging history records.

  • N Bourges
    N Bourges Member Posts: 3

    I find it interesting to get default values on update.

    Really easier for things like last update user, date information.

    But what do you suggest if you need a default value for insert and a default value for update?

    Example :

    last_action_type char(1 byte)

    Values code C : Create, M : Modify, R : Remove

    Regards

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown
    edited Dec 13, 2016 10:06AM

    I find it interesting to get default values on update.

    Really easier for things like last update user, date information.

    But what do you suggest if you need a default value for insert and a default value for update?

    Example :

    last_action_type char(1 byte)

    Values code C : Create, M : Modify, R : Remove

    Regards

    N Bourges wrote:I find it interesting to get default values on update.Really easier for things like last update user, date information.But what do you suggest if you need a default value for insert and a default value for update?Example :last_action_type char(1 byte)Values code C : Create, M : Modify, R : RemoveRegards

    Interesting consideration. I don't think my suggestion would cover this scenario. At least not completly.

    Currently there is only a DEFAULT ON NULL. Which can serve as a replacement for an BEFORE ROW INSERT trigger.

    The suggested syntax DEFAULT ON UPDATE would serve as the replacement of the BEFORE ROW UPDATE trigger.

    The problem with your example is that R=Remove sounds like a delete, but is not a delete but an update instead. Otherwise the record would have been gone. In this case such a default trigger would overwrite the "R" value to "M". You would still need you own logic to make the distinction.

    However one point here is interesting. maybe we would like some syntax that allows us to combine different default scenarios.

    e.g.

    DEFAULT ON NULL 'C' ON UPDATE 'M'

    William Robertson
  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    N Bourges wrote:I find it interesting to get default values on update.Really easier for things like last update user, date information.But what do you suggest if you need a default value for insert and a default value for update?Example :last_action_type char(1 byte)Values code C : Create, M : Modify, R : RemoveRegards

    Interesting consideration. I don't think my suggestion would cover this scenario. At least not completly.

    Currently there is only a DEFAULT ON NULL. Which can serve as a replacement for an BEFORE ROW INSERT trigger.

    The suggested syntax DEFAULT ON UPDATE would serve as the replacement of the BEFORE ROW UPDATE trigger.

    The problem with your example is that R=Remove sounds like a delete, but is not a delete but an update instead. Otherwise the record would have been gone. In this case such a default trigger would overwrite the "R" value to "M". You would still need you own logic to make the distinction.

    However one point here is interesting. maybe we would like some syntax that allows us to combine different default scenarios.

    e.g.

    DEFAULT ON NULL 'C' ON UPDATE 'M'

    The "delete" would be necessary, because you may have a delete trigger that is going to do something with the data as it gets deleted - and you may want a column to default to a specific value as it's deleted e.g. perhaps record changes (inserts, updates and deletes) are part of an process that generates XML to be sent off to another system so that the other system can action the record in some way.

    If you're going to have one, or two, you may as well have them all - people will always find a use for these things.

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    The "delete" would be necessary, because you may have a delete trigger that is going to do something with the data as it gets deleted - and you may want a column to default to a specific value as it's deleted e.g. perhaps record changes (inserts, updates and deletes) are part of an process that generates XML to be sent off to another system so that the other system can action the record in some way.

    If you're going to have one, or two, you may as well have them all - people will always find a use for these things.

    I like if changes are systematic and cover all cases. But I can not imagine any scenario where I really want a ON DELETE default value.

    All :NEW values in a row trigger are NULL during the delete. During the process I know that I'm doing a delete. And after the process the row is gone. For inserts and updates the value would still be there. There a default value makes sense.

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown

    I like if changes are systematic and cover all cases. But I can not imagine any scenario where I really want a ON DELETE default value.

    All :NEW values in a row trigger are NULL during the delete. During the process I know that I'm doing a delete. And after the process the row is gone. For inserts and updates the value would still be there. There a default value makes sense.

    The TIMESTAMP (based on SCN) for FLASHBACK DATA ARCHIVE enabled tables have a resolution of +- 5 seconds.

    If you want a higher resolution, you have to maintain the higher resolution value in a column.

    That is: For tables where I need a higher resolution, I would like to have the DEFAULT ON DELETE record that value in the FLASHBACK DATA ARCHIVE table.

    MK

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    The TIMESTAMP (based on SCN) for FLASHBACK DATA ARCHIVE enabled tables have a resolution of +- 5 seconds.

    If you want a higher resolution, you have to maintain the higher resolution value in a column.

    That is: For tables where I need a higher resolution, I would like to have the DEFAULT ON DELETE record that value in the FLASHBACK DATA ARCHIVE table.

    MK

    Interesting consideration. I was also thinking about RETURNING clause. But that would make sense only for a merge statement, which doesn't support returning clause yet.

  • user991291
    user991291 Member Posts: 25

    Hi All,

    Has anyone come across the error (00905. 00000 -  "missing keyword") when running the create statement? If I comment out or remove the UPDATED_DATE (with the date default on update sysdate), the TBL_TEST is created successfully, otherwise it fails.

    SQL:

    Create Table TBL_TEST (

        FIELD_ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY, --- PK

        BC_NAME VARCHAR2(256) CONSTRAINT NN_BC_GOA_ATTRIBUTE_NAME NOT NULL,

        BC_VARIABLE_NAME VARCHAR2(256),

        CREATED_DATE  TIMESTAMP DEFAULT SYSTIMESTAMP CONSTRAINT NN_BC_GOA_CREATED_DATE NOT NULL,

        UPDATED_DATE TIMESTAMP DEFAULT ON UPDATE SYSTIMESTAMP

    );

    DB Version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

    Any help would be greatly appreciated.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond

    Hi All,

    Has anyone come across the error (00905. 00000 -  "missing keyword") when running the create statement? If I comment out or remove the UPDATED_DATE (with the date default on update sysdate), the TBL_TEST is created successfully, otherwise it fails.

    SQL:

    Create Table TBL_TEST (

        FIELD_ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY, --- PK

        BC_NAME VARCHAR2(256) CONSTRAINT NN_BC_GOA_ATTRIBUTE_NAME NOT NULL,

        BC_VARIABLE_NAME VARCHAR2(256),

        CREATED_DATE  TIMESTAMP DEFAULT SYSTIMESTAMP CONSTRAINT NN_BC_GOA_CREATED_DATE NOT NULL,

        UPDATED_DATE TIMESTAMP DEFAULT ON UPDATE SYSTIMESTAMP

    );

    DB Version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

    Any help would be greatly appreciated.

    user991291 wrote:Hi All,Has anyone come across the error (00905. 00000 - "missing keyword") when running the create statement? If I comment out or remove the UPDATED_DATE (with the date default on update sysdate), the TBL_TEST is created successfully, otherwise it fails.SQL:Create Table TBL_TEST ( FIELD_ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY, --- PK BC_NAME VARCHAR2(256) CONSTRAINT NN_BC_GOA_ATTRIBUTE_NAME NOT NULL, BC_VARIABLE_NAME VARCHAR2(256), CREATED_DATE TIMESTAMP DEFAULT SYSTIMESTAMP CONSTRAINT NN_BC_GOA_CREATED_DATE NOT NULL, UPDATED_DATE TIMESTAMP DEFAULT ON UPDATE SYSTIMESTAMP);DB Version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionAny help would be greatly appreciated.

    Your database is 12.2... but what is your client version?  It looks to me like your client version is lower and isn't aware of newer features of the database perhaps?