Thanks for the question, Dusan.

Asked: March 25, 2022 - 10:51 am UTC

Last updated: March 31, 2022 - 10:11 am UTC

Version: 19c

Viewed 10K+ times! This question is

You Asked

Dear AskTom team,

I want to achieve the column in a table to be populated on insert by systimestamp, no matter whether by default on null, or whether a user tries to insert some value to the column by insert statement. And to avoid any attempt to update the column by a different value than the current systimestamp.
Yes, it is possible to achieve this by triggers, but I want to avoid triggers due known negative impacts.

So, how to fully change trigger functionality below by a different way (defaults etc):

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

valasekd@PDB1> create table t (id number not null, dt timestamp);

Table created.
  1  create or replace trigger t_bir before insert or update on t for each row
  2  begin
  3  :new.dt :=systimestamp;
  4* end;
valasekd@PDB1> /

Trigger created.

valasekd@PDB1> select sysdate from dual;

SYSDATE
---------
25-MAR-22

valasekd@PDB1> insert into t values(1,sysdate+5); --immutable to different value as sysdate

1 row created.

valasekd@PDB1> select sysdate from dual;

SYSDATE
---------
25-MAR-22

valasekd@PDB1> select sysdate from t;

SYSDATE
---------
25-MAR-22

valasekd@PDB1> update t set dt=sysdate+5; --immutable to update

1 row updated.

valasekd@PDB1> select sysdate from t;

SYSDATE
---------
25-MAR-22

valasekd@PDB1>

Thanks,

Dusan

and Chris said...

I don't know of a good way to stop people inserting values other than the default; you may be stuck with a trigger here. If you do large multi-row inserts, it may be worth looking into compound triggers to lower the overhead.

Using a trigger to prevent updates is trivial though - set it to raise an exception whenever you're updating that column:

create table t (
  id number 
    not null, 
  dt timestamp 
    not null
);

create or replace trigger t_no_date_change_bir 
before insert on t 
for each row
begin
  :new.dt := systimestamp;
end;
/

create or replace trigger t_no_date_change_bur 
before update of dt on t 
begin
  raise_application_error ( -20001, q'[You can't change that!]' );
end;
/

insert into t ( id ) values ( 1 );

select * from t;

        ID DT                            
---------- ------------------------------
         1 28-MAR-2022 13.16.56.506199000

update t
set    dt = systimestamp;

ORA-20001: You can't change that!
ORA-06512: at "CHRIS.T_NO_DATE_CHANGE_BIR", line 2
ORA-04088: error during execution of trigger 'CHRIS.T_NO_DATE_CHANGE_BIR'

select * from t;

        ID DT                            
---------- ------------------------------
         1 28-MAR-2022 13.16.56.506199000

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Dusan, March 28, 2022 - 2:30 pm UTC

Hi Chris,

You may did not understand what I asked, I did not stuck with trigger, that is something I want to avoid. I asked for a different way of achieving described trigger functionality.
I asked:
"So, how to fully change trigger functionality below by a different way (defaults etc):"

That means, avoiding trigger functionality.

Once the row is inserted, the column gets a value, and the value will not change. Column value will be immutable. The true timestamp. With no trigger protection.

Thanks,

Dusan

March 28, 2022 - 4:51 pm UTC

Quote:

I don't know of a good way to stop people inserting values other than the default; you may be stuck with a trigger here.

If you're happy to allow people to supply their own values for the date, then:

default on null systimestamp not null

Is a better solution.

But if you absolutely must always use systimestamp as the insert value, I don't know of a better solution than triggers.

Personally, I see no issue with the trigger for stopping the update - there's no performance overhead as it stops the statement immediately. The only thing you need to watch for is people disabling the trigger (though of course, that may be necessary/desirable in some cases).

Dusan, March 29, 2022 - 12:00 pm UTC

So, possibilities are limited ....
And this is required as long as I work with Oracle database. Columns like date_created, created_by. E.G. transaction table, there must be sysdate or systimestamp as for date_created, no any other date, datetime value.
Unfortunately, this might be enforced by triggers only.

I wish there was default definition as DEFAULT <value> UNCHANGEABLE.

Another thing on my wishlist.

March 29, 2022 - 12:48 pm UTC

If there are features you'd like us to add you can speak with support to see about getting these added.

You could also submit them to the database ideas forum so we can see what support these have in the community:

https://community.oracle.com/tech/apps-infra/categories/database-ideas-ideas

grant insert(columns), update(columns)

writer, March 30, 2022 - 2:52 am UTC

To restrict column modification, do not grant insert and update it. The owner can still change the value. You can also create a view with expression instead of the original column.

March 30, 2022 - 1:18 pm UTC

Not granting insert/update to other users is a good idea. But doesn't really address the problem here - the OP still wants to be able insert/update rows. Plus a user is always able to insert/update rows in their own tables.

You can also create a view with expression instead of the original column.

That can help in some cases, but the expression in question here is SYSTIMESTAMP. So it'll always return "now" instead of the time of insert.

That said Connor has come up with a non-trigger solution using VPD which could be worth exploring:

https://connor-mcdonald.com/2022/03/30/enforcing-default-values/

why?

reader, March 31, 2022 - 6:46 am UTC

Not granting insert/update to other users is a good idea. But doesn't really address the problem here - the OP still wants to be able insert/update rows. Plus a user is always able to insert/update rows in their own tables.

Not granting insert/update on the certain columns do not restrict insert/update of others columns.

That can help in some cases, but the expression in question here is SYSTIMESTAMP. So it'll always return "now" instead of the time of insert.

create view v as select col1, col2, restricted_col+0 restricted_col from t;

Now you can insert, update, delete, but do not modify restricted_col using this view.

March 31, 2022 - 10:11 am UTC

The OP asked about disabling ALL modifications to the column - I'm assuming this also means by the table owner too!

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

grant insert(columns), update(columns)

why?

More to Explore

PL/SQL demos

PL/SQL docs

Before insert row trigger vs defaults

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

grant insert(columns), update(columns)

why?

More to Explore

PL/SQL demos

PL/SQL docs