Solved

DB2 SQL Trigger - Date Stamp single row update

Posted on 2009-04-08
10
1,404 Views
Last Modified: 2012-05-06
Hi All, I need to be able to update a single row in a DB table using a trigger so I can log when a particular row was last updated.

I have reviewed similar questions which point to using  the Getdate() function but this doesn't seem to be avaliable in db2

Is there an alternative I can use?

Thanks in advance

0
Comment
Question by:taylor99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 12

Expert Comment

by:Dimitris
ID: 24095738
The CURRENT DATE special register gives the current system date and CURRENT TIMESTAMP the current system date and time.

sample

SELECT CURRENT TIMESTAMP
FROM SYSIBM.SYSDUMMY1
or
ELECT CURRENT DATE
FROM SYSIBM.SYSDUMMY1
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 24096237
Hi taylor,

Dan's got it right.  :)

Putting the whole thing into context, the trigger would look something like this:

CREATE TRIGGER timestamp_mytable
  NO CASCADE BEFORE UPDATE ON mytable
  REFERENCING OLD AS o NEW AS n
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    SET n.updatetime = current timestamp
END

Good Luck,
Kent
0
 

Author Comment

by:taylor99
ID: 24096738
Thanks, so how would I then use this to update a specific cell in a single DB row when another cell in that row is updated?

Below is what I've attempted to use but the whole column is updated.  Can you use an alternative for the FOR EACH ROW statement which will point to a specific row?

CREATE TRIGGER SCHEMA."TABLE1" AFTER  UPDATE OF PRICE ON TABLE1
FOR EACH ROW  
MODE DB2SQL
UPDATE TABLE1
   SET DATE_ENTERED = CURRENT DATE
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 12

Expert Comment

by:Dimitris
ID: 24096836
The trigger you have write will update the ENTIRE TABLE each time
I think that the solution has been provided for this issue by Kdo
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 24096958
Hi taylor.

Your trigger is close, but misses a critical subtlety.

Look that the UPDATE...SET portion of your trigger.  If you execute that you will update the DATE_ENTERED field for every row in the table.  Since what you want is to update only the modified row, you need to tell the trigger to filter out the unchanged rows.

You do that with the REFERENCING clause.  

  REFERENCING OLD AS o NEW AS n

Within the trigger, n.date_entered is the column (field) in the current (changed) row that you want to update.  Omitting the 'n.' causes the change to be cascaded through the entire table.

CREATE TRIGGER SCHEMA."TABLE1"
AFTER  UPDATE OF PRICE ON TABLE1
  REFERENCING OLD AS o NEW AS n
FOR EACH ROW  
MODE DB2SQL
UPDATE TABLE1
   SET n.DATE_ENTERED = CURRENT DATE



Good Luck,
Kent
0
 
LVL 27

Expert Comment

by:tliotta
ID: 24101484
taylor99:

What platform/operating system? What version/release of DB2?

Also, I can imagine two scenarios that kind of fit with what you've said. Can you clarify which one is correct?

  1. You have a table that clients update. The table has a column for a Last_Change TIMESTAMP. You want a trigger to populate that column with CURRENT TIMESTAMP whenever a client updates the row (or when a row is inserted perhaps).

  2. You have two tables. Clients update the first table. You want a trigger to log all updates into the second table, including a CURRENT TIMESTAMP value.
Tom
0
 

Author Comment

by:taylor99
ID: 24105387
Hi Kent,

Thanks for your input, sorry if my reply yesterday didn't make sense.  I hadn't refreshed my browser so hadn't seen your reply.  This seems to be exactly what I'm after but having attempted to create the trigger using your example I get the error below, although I assure you DATE_ENTERED is a valid column in my table.  

"DATE_ENTERED" is not valid in the context where it is used.

My trigger code is

CREATE TRIGGER SCHEMA."TABLE1 INTERNAL  SET PRICE UPDATE DATE STAMP" NO CASCADE
BEFORE  UPDATE OF PRICE ON SCHEMA.TABLE1  
REFERENCING  OLD AS OROW  NEW AS NROW  FOR EACH ROW  
MODE DB2SQL
UPDATE SCHEMA.TABLE1
   SET NROW.DATE_ENETERED = CURRENT DATE
0
 
LVL 12

Accepted Solution

by:
Dimitris earned 500 total points
ID: 24105427
try this

CREATE TRIGGER SCHEMA."TABLE1 INTERNAL  SET PRICE UPDATE DATE STAMP" NO CASCADE
BEFORE  UPDATE OF PRICE ON SCHEMA.TABLE1  
REFERENCING  OLD AS OROW  NEW AS NROW  FOR EACH ROW  
MODE DB2SQL
BEGIN ATOMIC
   SET NROW.DATE_ENETERED = CURRENT DATE

 
0
 

Author Comment

by:taylor99
ID: 24105432
Hi Tom,

In answer to your questions I am running DB2 9.5.2 under Windows XP and the first senario you suggested is the correct one.

Cheers
0
 

Author Closing Comment

by:taylor99
ID: 31567928
Spot on thanks guys
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question