newbie Q: How to auto insert current timestep with each new record

[r***@chrysler.com]

Is there a simple clause that can be added to a create table statement,
such that each insert/update to the table automatically writes the current timestamp into a specific column?

The goal is to have a field indicating when each record was last updated,
without having to add a clause to each insert/update statement.

Is there a way to use not null default?
Is there a way to use trigger?
Is there a better way?

TIA,

[Blair Kenneth Adamache]

Post by r***@chrysler.com
Is there a simple clause that can be added to a create table statement,
such that each insert/update to the table automatically writes the current timestamp into a specific column?
The goal is to have a field indicating when each record was last updated,
without having to add a clause to each insert/update statement.
Is there a way to use not null default?
Is there a way to use trigger?
Is there a better way?
TIA,

Create the table with a generated column for each row. Define that
column as current timestamp.

You can also use triggers.





Create a trigger for each update:

[Knut Stolze]

Post by Blair Kenneth Adamache

Post by r***@chrysler.com
Is there a simple clause that can be added to a create table statement,
such that each insert/update to the table automatically writes the
current timestamp into a specific column?
The goal is to have a field indicating when each record was last updated,
without having to add a clause to each insert/update statement.
Is there a way to use not null default?

Yes, use DEFAULT CURRENT TIMESTAMP.

Post by Blair Kenneth Adamache

Post by r***@chrysler.com
Is there a way to use trigger?
Is there a better way?
TIA,

Create the table with a generated column for each row. Define that
column as current timestamp.

DB2 doesn't like CURRENT TIMESTAMP or other non-deterministic special
registers in the definition for generated columns. It raises error
SQL0548N.