Update trigger to update a time column ..

Shefu · #1 (**permalink**) 12-11-06, 04:41

Hi,

I ve a test table defined with the following columns,

Code:

CREATE TABLE TEST(ID INT, TIME DATE);

Here, whenever any of the table column is updated, the 'TIME' column should be updated with the date and time of update.

Kindly let me know could i ve any simple triggers for this.

Regards,
Sn

guyprzytula · #2 (**permalink**) 12-11-06, 07:36

it is recommended not to use reserved words for column names : as time
create a column with datatype timestamp to have date and time
sample trigger :
CREATE TRIGGER RECORD_HISTORY
AFTER UPDATE OF QUOTE ON CURRENTQUOTE
REFERENCING NEW AS NEWQUOTE
FOR EACH ROW
BEGIN ATOMIC
update QUOTEHISTORY
set quote=current timestamp;
END

urquel · #3 (**permalink**) 12-11-06, 09:49

How about a before update trigger that overrides the transition variable.

Code:

CREATE TRIGGER TEST_BU
AFTER UPDATE OF "TIME" ON TEST
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN ATOMIC
SET NEW."TIME" = CURRENT TIMESTAMP;
END

grofaty · #4 (**permalink**) 12-12-06, 01:43

urquel,
this trigger is working well, but you probably mean "before update"...
The sintax "REFERENCING OLD AS OLD NEW AS NEW" is not needed it can be only: "REFERENCING NEW AS NEW".

Code:

CREATE TRIGGER TEST_BU
BEFORE UPDATE OF "TIME" ON TEST
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN ATOMIC
SET NEW."TIME" = CURRENT TIMESTAMP;
END@

Save the file to file.sql and execute it by:
db2 -td@ -f file.sql

Hope this helps,
Grofaty

Shefu · #5 (**permalink**) 12-13-06, 02:55

Thanks for the scripts guyz.. will let you know once i tested it.

thanks again for the help

Cheers
Sn

Shefu · #6 (**permalink**) 12-13-06, 03:25

Hi,

As per the advice, i ve tried the below trigger

Code:

CREATE TRIGGER TRIG_TEST
AFTER UPDATE ON test
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
update test SET col3 = CURRENT TIMESTAMP;
END
@

on my table,

Code:

create table test (col1 int, col2 int, col3 time stamp)

Trigger compiled successfully, but it was not triggered when the following update call is made,

Code:

update test set col1 = 11 where col1 = 1

the error thrown is ,

Code:

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0723N  An error occurred in a triggered SQL statement in trigger
"ADMINISTRATOR.TRIG_TEST".  Information returned for the error includes
SQLCODE "-724", SQLSTATE "54038" and message tokens
"ADMINISTRATOR.TRIG_TEST".  SQLSTATE=09000

It does not allow me change the Trigger as 'Before' Trigger and to include the 'NEW.' in the update statement defined in the trigger body.

any help to resolve this issue will be appreciated.

Thanks,
Shefu

grofaty · #7 (**permalink**) 12-13-06, 05:03

Hi,
there is no "FOR" statement in your trigger and no NEW statement. IF you are referencing to NEW statement there can not be update cause in begin atomic statement!

If you want to trigger get triggered before update of col1 and NOT before update of COL2 then try this out:

Code:

CREATE TRIGGER TRIG_TEST
BEFORE UPDATE OF COL1 ON TEST
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN ATOMIC
SET NEW.COL3 = CURRENT TIMESTAMP;
END@

If you want to trigger get triggered before update of col1 and ALSO COL2 then try this out:

Code:

CREATE TRIGGER TRIG_TEST
BEFORE UPDATE OF COL1, COL2 ON TEST
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN ATOMIC
SET NEW.COL3 = CURRENT TIMESTAMP;
END@

Hope this helps,
Grofaty

Shefu · #8 (**permalink**) 12-13-06, 05:31

Thanks for the solution Grofaty, after including the 'OF' clause it works fine..

cheers
Sn

Shefu · #9 (**permalink**) 12-13-06, 07:32

HI,

Am sorry that I am not able to compile the below suggested trigger,

Code:

CREATE TRIGGER TRIG_TEST
AFTER UPDATE OF col1, col2 ON test
REFERENCING NEW AS NEW
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC

    UPDATE test SET NEW.col3 = CURRENT TIMESTAMP;

END
@

In the trigger body, when the NEW clause is used in the update statement, compilation error is thrown,

if i remove the "NEW. " , the trigger is compiled and the output is wrong.

DB2 version is 8.0
OS : Windows2k

grofaty · #10 (**permalink**) 12-13-06, 09:00

Hi,
why do you use 'update statement' use just: SET NEW.COL3 = CURRENT TIMESTAMP;
See my sample above.

Quote:

Originally Posted by Shefu

I am not able to compile the below suggested trigger

What do you mean by "compile"?

Please also provide any error message if something is not working.

Quote:

Originally Posted by Shefu

if i remove the "NEW. " , the trigger is compiled and the output is wrong.

That is reasonable. Using "UPDATE test SET col3 = CURRENT TIMESTAMP" all the records in table are updated. The NEW statement only updates the records that are updated in original update statement (update test set col1 = 11 where col1 = 1) so only where col1=1. You must use NEW in trigger to update only affected records.

Try copy/paste my suggested trigger statement.

Hope this helps,
Grofaty

ARWinner · #11 (**permalink**) 12-13-06, 10:00

Shefu,
It does not look like you have looked at Grofaty's trigger closely. You need to use a "BEFORE UPDATE" trigger not an "AFTER UPDATE".

Andy

Shefu · #12 (**permalink**) 12-14-06, 01:32

Hi,

I guess i ve told about the errors i got while using BEFORE trigger and NEW clause in one of my previous posts, any way let me tell you what i did, i tried ur code directly for my test table, here goes the first trigger,

Code:

CREATE TRIGGER TRIG_TEST
BEFORE UPDATE OF col1, col2 ON TEST
REFERENCING NEW AS NEW
FOR EACH ROW
MODE DB2SQL

BEGIN ATOMIC

	SET NEW.col3 = CURRENT TIMESTAMP;

END
@

The following error thrown :

Code:

C:\DAdministrator>db2 -td@ -f grafTrig.db2
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "BEFORE" was found following "E TRIGGER
TRIG_TEST ".  Expected tokens may include:  "AFTER".  LINE NUMBER=2.
SQLSTATE=42601

Hence changed the Before to AFTER and the trigger looked like,

Code:

CREATE TRIGGER TRIG_TEST
AFTER UPDATE OF col1, col2 ON TEST
REFERENCING NEW AS NEW
FOR EACH ROW
MODE DB2SQL

BEGIN ATOMIC

	SET NEW.col3 = CURRENT TIMESTAMP;

END
@

Again an error bursted out, as follows :

Code:

C:\DAdministrator>db2 -td@ -f grafTrig.db2
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0797N  The trigger "ADMINISTRATOR.TRIG_TEST" is defined with an unsupported
triggered SQL statement.  LINE NUMBER=12.  SQLSTATE=42987

again in the trigger, changed the SET statement to UPDATE statement :

Code:

CREATE TRIGGER TRIG_TEST
AFTER UPDATE OF col1, col2 ON TEST
REFERENCING NEW AS NEW
FOR EACH ROW
MODE DB2SQL

BEGIN ATOMIC 

	UPDATE TEST SET NEW.col3 = CURRENT TIMESTAMP;

END
@

For the above change i got the below error,

Code:

C:\DAdministrator>db2 -td@ -f  grafTrig.db2
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0206N  "COL3" is not valid in the context where it is used.  LINE NUMBER=9.
SQLSTATE=42703

Kindly let me know whats going on wrong here..

Regards,
Sn

ARWinner · #13 (**permalink**) 12-14-06, 08:12

Your syntax for the BEFORE UPDATE trigger is wrong. You should check the SQL Reference manual for the correct syntax. It should look like this:

CREATE TRIGGER TRIG_TEST
NO CASCADE BEFORE UPDATE OF col1, col2 ON TEST
REFERENCING NEW AS NEW
FOR EACH ROW
MODE DB2SQL

BEGIN ATOMIC

SET NEW.col3 = CURRENT TIMESTAMP;

END
@

Andy

Wim · #14 (**permalink**) 12-14-06, 11:52

I use this trigger to keep information on who was the latest one to alter a record and when it was done. You could also add an extra column nr_maint to keep track of the number of times the record was altered.

By not explicitely specifying the columns, any UPDATE on the record will fire the trigger.

Code:

CREATE TRIGGER mytrigger
NO CASCADE BEFORE UPDATE ON table1
REFERENCING
OLD AS pre
NEW AS post
FOR EACH ROW MODE DB2SQL
SET
	post.da_maint = CURRENT TIMESTAMP,
	post.us_maint = USER,
	post.nr_maint = pre.nr_maint + 1;

grofaty · #15 (**permalink**) 02-02-09, 02:52

Hi,
just to update two years old thread.

To get current timestamp column automatically refreshed after each insert/update there is new feature in v9.5.

Sample:
CREATE TABLE ADMIN.PERSON
(
PERSONID INT NOT NULL PRIMARY KEY,
NAME CHAR(20),
LAST_TIMESTAMP TIMESTAMP NOT NULL GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
) ;

More info: DB2utor: Automate Row Change Timestamp Used for Optimistic Locking

Hope this helps someone out there, because it is really excellent functionality.
Regards,
Grofaty

Internet Services

Web Development

Digital Marketing

Consumer Tech