taylor99
asked on
DB2 SQL Trigger - Date Stamp single row update
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
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
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
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
ASKER
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
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
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
I think that the solution has been provided for this issue by Kdo
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
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
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?
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?
- 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).
- 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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
Spot on thanks guys
sample
SELECT CURRENT TIMESTAMP
FROM SYSIBM.SYSDUMMY1
or
ELECT CURRENT DATE
FROM SYSIBM.SYSDUMMY1