Automatically generated TIMESTAMP field by INSERT and UPDATE operations 1

[mikrom]

Hello,

I'm on DB2 UDB for IBM iSeries V5R4. I have a table which contains TIMESTAMP field and want to declare the field so, that it should be automatically updated by UPDATE and INSERT operations.

I created the table as follows

[COLOR=#804040][b]CREATE[/b][/color] [COLOR=#6a5acd]TABLE[/color] ex_tmstp (
  [COLOR=#0000ff]-- automatically incremented key field[/color]
  id INT [COLOR=#804040][b]NOT[/b][/color] [COLOR=#6a5acd]NULL[/color] GENERATED ALWAYS [COLOR=#6a5acd]AS[/color] IDENTITY (
    [COLOR=#6a5acd]START[/color] [COLOR=#6a5acd]WITH[/color] [COLOR=#ff00ff]1[/color] [COLOR=#6a5acd]INCREMENT[/color] [COLOR=#6a5acd]BY[/color] [COLOR=#ff00ff]1[/color]
    NO MINVALUE NO MAXVALUE
    CYCLE NO [COLOR=#6a5acd]ORDER[/color]
    CACHE [COLOR=#ff00ff]20[/color] ) PRIMARY KEY,
  [COLOR=#0000ff]--[/color]
  data [COLOR=#2e8b57][b]VARCHAR[/b][/color]([COLOR=#ff00ff]50[/color]),
  [COLOR=#0000ff]-- Warning: [/color]
  [COLOR=#0000ff]-- This creates TIMESTAMP field which will be automatically generated [/color]
  [COLOR=#0000ff]-- only by INSERT but not by UPDATE operation[/color]
  tmstp TIMESTAMP [COLOR=#6a5acd]WITH[/color] [COLOR=#6a5acd]DEFAULT[/color] [COLOR=#6a5acd]CURRENT[/color] TIMESTAMP
)
;

When I try to INSERT some rows using

[COLOR=#804040][b]INSERT[/b][/color] [COLOR=#6a5acd]INTO[/color] ex_tmstp (data)
  [COLOR=#6a5acd]VALUES[/color] ([COLOR=#ff00ff]'The time of creation is:'[/color])
;

then the TIMESTAMP field will be automatically correctly inserted.

But when I try to UPDATE a row with

[COLOR=#804040][b]UPDATE[/b][/color] ex_tmstp
  [COLOR=#804040][b]SET[/b][/color] data=[COLOR=#ff00ff]'The current timestamp of last update is: '[/color]
[COLOR=#6a5acd]WHERE[/color] id=[COLOR=#ff00ff]1[/color]
;

then the TIMESTAMP field tmstp will not be automatically updated. To achieve this I need to create a trigger

[COLOR=#0000ff]-- for UPDATE this trigger is necessary[/color]
[COLOR=#804040][b]CREATE[/b][/color] [COLOR=#6a5acd]TRIGGER[/color] update_cur_tmstp
  BEFORE [COLOR=#804040][b]UPDATE[/b][/color]
  [COLOR=#6a5acd]ON[/color] ex_tmstp
  REFERENCING NEW [COLOR=#6a5acd]AS[/color] n
  [COLOR=#6a5acd]FOR[/color] EACH [COLOR=#6a5acd]ROW[/color]
  [COLOR=#804040][b]SET[/b][/color] tmstp = [COLOR=#6a5acd]CURRENT[/color] TIMESTAMP
;

Isn't it possible to declare the TIMESTAMP field tmstp so it be automatic updated without need to add a trigger ?
Or is there a better way to do what I have done?

 

[MarcLodge]

Most sites I've worked at have had tables with last updated timestamp, and we have always done this programatically. It's always been part of the standard to code it in the SQL.

Your way is the only other way I can think of, but as you can't do it all in the DDL, I'd probably leave that bit out and do it all in the trigger, just so that when it comes to maintenance, it's all in once place.

Marc

 

[mikrom]

Hi Marc,

Thank you very much.

You mean something like this without using WITH DEFAULT CURRENT TIMESTAMP and to create two triggers - one for INSERT and other for UPDATE?

[COLOR=#804040][b]CREATE[/b][/color] [COLOR=#6a5acd]TABLE[/color] ex_tmstp (
  [COLOR=#0000ff]-- automatically incremented key field[/color]
  id INT [COLOR=#804040][b]NOT[/b][/color] [COLOR=#6a5acd]NULL[/color] GENERATED ALWAYS [COLOR=#6a5acd]AS[/color] IDENTITY (
    [COLOR=#6a5acd]START[/color] [COLOR=#6a5acd]WITH[/color] [COLOR=#ff00ff]1[/color] [COLOR=#6a5acd]INCREMENT[/color] [COLOR=#6a5acd]BY[/color] [COLOR=#ff00ff]1[/color]
    NO MINVALUE NO MAXVALUE
    CYCLE NO [COLOR=#6a5acd]ORDER[/color]
    CACHE [COLOR=#ff00ff]20[/color] ) PRIMARY KEY,
  [COLOR=#0000ff]--[/color]
  data [COLOR=#2e8b57][b]VARCHAR[/b][/color]([COLOR=#ff00ff]50[/color]),
  [COLOR=#0000ff]-- Warning: [/color]
  [COLOR=#0000ff]-- This creates TIMESTAMP field which will be automatically generated [/color]
  [COLOR=#0000ff]-- only by INSERT but not by UPDATE operation[/color]
  tmstp TIMESTAMP [COLOR=#0000ff]--WITH DEFAULT CURRENT TIMESTAMP[/color]
)
;

[COLOR=#0000ff]-- for UPDATE this trigger is necessary[/color]
[COLOR=#804040][b]CREATE[/b][/color] [COLOR=#6a5acd]TRIGGER[/color] update_cur_tmstp
  BEFORE [COLOR=#804040][b]UPDATE[/b][/color]
  [COLOR=#6a5acd]ON[/color] ex_tmstp
  REFERENCING NEW [COLOR=#6a5acd]AS[/color] n
  [COLOR=#6a5acd]FOR[/color] EACH [COLOR=#6a5acd]ROW[/color]
  [COLOR=#804040][b]SET[/b][/color] tmstp = [COLOR=#6a5acd]CURRENT[/color] TIMESTAMP
;

[COLOR=#0000ff]-- for INSERT this trigger is necessary [/color]
[COLOR=#0000ff]-- only if in CREATE statement we don't use WITH DEFAULT CURRENT TIMESTAMP[/color]
[COLOR=#804040][b]CREATE[/b][/color] [COLOR=#6a5acd]TRIGGER[/color] insert_cur_tmstp
  BEFORE [COLOR=#804040][b]INSERT[/b][/color]
  [COLOR=#6a5acd]ON[/color] ex_tmstp
  REFERENCING NEW [COLOR=#6a5acd]AS[/color] n
  [COLOR=#6a5acd]FOR[/color] EACH [COLOR=#6a5acd]ROW[/color]
  [COLOR=#804040][b]SET[/b][/color] tmstp = [COLOR=#6a5acd]CURRENT[/color] TIMESTAMP
;

Isn't it possible to create only one trigger which will serve for both operations INSERT and UPDATE together, so the maintenance would be on one place?

 

[MarcLodge]

I believe you have to have a trigger for each of the possible update actions. We have a library which stores all of our triggers and each is uniquely identified with a 6 character prefix which identifies the table, followed by either a U, and A or a D, then lastly a numeric digit.

That way, we keep all our triggers on a specific table grouped together and they are easy to find and maintain.

Hope this helps.
Marc