Here is an example of a solution. Note that "PAYLOAD" in the table can be any number
of columns, but must not include the timestamp LAST_UPDATE, or the trigger will
get a -724 error because you will recurse forever updating LAST_UPDATE when there
is an update to LAST_UPDATE when....


!echo --- Creating Table TEST_table ---;

DROP TABLE TEST_table;
CREATE TABLE TEST_table (

main_key INTEGER NOT NULL WITH DEFAULT,
last_update TIMESTAMP NOT NULL WITH DEFAULT,
payload VARCHAR(30) NOT NULL WITH DEFAULT,

PRIMARY KEY (main_key)
) IN SRI;


!echo --- Creating Trigger TEST_table ---;

DROP TRIGGER TEST_table_ts;
CREATE TRIGGER TEST_table_ts
AFTER UPDATE OF payload ON TEST_table
REFERENCING new AS n
FOR EACH ROW MODE DB2SQL
UPDATE TEST_table SET last_update = CURRENT TIMESTAMP WHERE main_key = n.main_key;


INSERT INTO TEST_table ( main_key, payload ) VALUES
( 1, 'row 1' ),
( 2, 'row 2' ),
( 3, 'row 3' ),
( 4, 'row 4' ),
( 5, 'row 5' ),
( 6, 'row 6' ),
( 7, 'row 7' ),
( 8, 'row 8' );


UPDATE TEST_table SET payload = 'Fred and Wilma' WHERE main_key = 5;
UPDATE TEST_table SET payload = 'Barney and Betty' WHERE main_key = 7;

SELECT * FROM TEST_table;

DROP TABLE TEST_table;




On Tue, 4 Mar 2003 09:26:25 -0500, [Email Address Removed] wrote:

>
>I have a table that has a datetime column that is used to give the
>timestamp of the last time that row was modified. I am trying to write an
>update trigger that will update that field to CURRENT TIMESTAMP every
>time that row is updated. I get error 0900 (general trigger error) no
>matter what I do. Can I get some guidance on how to implement this
>please.
>
>Thanks,
>Lisa Greetham
>Database Administrator, Corporate Internet Group
>Bank One
>
>
>
>
>This transmission may contain information that is privileged, confidential and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you.
>
>
>---------------------------------------------------------------------
>PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
>To unsubscribe, e-mail: db2udbdba-[Email Address Removed] additional commands, e-mail: db2udbdba-[Email Address Removed] J Reppert
Sumx Inc.
972-355-8004 x102
972-355-8048 fax


DB2 & UDB email list listserv db2-l LazyDBA home page