INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Automatically generated TIMESTAMP field by INSERT and UPDATE operations

Automatically generated TIMESTAMP field by INSERT and UPDATE operations

Automatically generated TIMESTAMP field by INSERT and UPDATE operations

(OP)
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

CODE

CREATE TABLE ex_tmstp (
  -- automatically incremented key field
  id INT NOT NULL GENERATED ALWAYS AS IDENTITY (
    START WITH 1 INCREMENT BY 1
    NO MINVALUE NO MAXVALUE
    CYCLE NO ORDER
    CACHE 20 ) PRIMARY KEY,
  --
  data VARCHAR(50),
  -- Warning: 
  -- This creates TIMESTAMP field which will be automatically generated 
  -- only by INSERT but not by UPDATE operation
  tmstp TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP
)
; 

When I try to INSERT some rows using

CODE

INSERT INTO ex_tmstp (data)
  VALUES ('The time of creation is:')
; 
then the TIMESTAMP field will be automatically correctly inserted.

But when I try to UPDATE a row with

CODE

UPDATE ex_tmstp
  SET data='The current timestamp of last update is: '
WHERE id=1
; 

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

CODE

-- for UPDATE this trigger is necessary
CREATE TRIGGER update_cur_tmstp
  BEFORE UPDATE
  ON ex_tmstp
  REFERENCING NEW AS n
  FOR EACH ROW
  SET tmstp = CURRENT 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?

RE: Automatically generated TIMESTAMP field by INSERT and UPDATE operations

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

RE: Automatically generated TIMESTAMP field by INSERT and UPDATE operations

(OP)
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?

CODE

CREATE TABLE ex_tmstp (
  -- automatically incremented key field
  id INT NOT NULL GENERATED ALWAYS AS IDENTITY (
    START WITH 1 INCREMENT BY 1
    NO MINVALUE NO MAXVALUE
    CYCLE NO ORDER
    CACHE 20 ) PRIMARY KEY,
  --
  data VARCHAR(50),
  -- Warning: 
  -- This creates TIMESTAMP field which will be automatically generated 
  -- only by INSERT but not by UPDATE operation
  tmstp TIMESTAMP --WITH DEFAULT CURRENT TIMESTAMP
)
;

-- for UPDATE this trigger is necessary
CREATE TRIGGER update_cur_tmstp
  BEFORE UPDATE
  ON ex_tmstp
  REFERENCING NEW AS n
  FOR EACH ROW
  SET tmstp = CURRENT TIMESTAMP
;

-- for INSERT this trigger is necessary 
-- only if in CREATE statement we don't use WITH DEFAULT CURRENT TIMESTAMP
CREATE TRIGGER insert_cur_tmstp
  BEFORE INSERT
  ON ex_tmstp
  REFERENCING NEW AS n
  FOR EACH ROW
  SET tmstp = CURRENT 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?

RE: Automatically generated TIMESTAMP field by INSERT and UPDATE operations

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close