Hi...
I have a simple question for one who is experienced...
In all our tables we have the two fields Edit_Who and Edit_When:
KL_Edit_Who VarChar(30) DEFAULT USER,
KL_Edit_When TIMESTAMP DEFAULT CURRENT TIMESTAMP,

In order to get those updated on each update we have to revert to triggers.
So I wrote two versions neither works...
CREATE TRIGGER KL_Key_LastGen_Upd
AFTER INSERT ON DBO.KL_Key_LastGen
FOR EACH ROW MODE DB2SQL
UPDATE DBO.KL_Key_LastGen SET KL_Edit_Who = USER, KL_Edit_When = CURRENT TIMESTAMP

CREATE TRIGGER KL_Key_LastGen_Upd
AFTER INSERT ON DBO.KL_Key_LastGen
REFERENCING NEW_TABLE AS NEWEMPS
FOR EACH STATEMENT MODE DB2SQL
UPDATE DBO.KL_Key_LastGen K SET KL_Edit_Who = USER, KL_Edit_When = CURRENT TIMESTAMP
WHERE KL_Table_Key IN( SELECT NEWEMPS.KL_Table_Key FROM NEWEMPS)

Anyone care to shed some light on my errors ???
Thanks
JR

When you say neither works, does it mean you get an error ?

If so, please post the message

Cheers
Sathyaram

Actually Nothing happens...
It accepts the trigger, but nothing get's executed.
(even though the first one could have updated the whole table.)
That's what's really got me stomped.
Thanks
JR

Are you sure the trigger is valid ?

Check the VALID column in SYSCAT.TRIGGERS

Sathyaram

I would try something like:

CREATE TABLE KL_Key_LastGen (
KL_Edit_Who varchar(50),
KL_Edit_When timestamp,
xxx int
)@


CREATE TRIGGER KL_Key_LastGen_Upd
NO CASCADE BEFORE INSERT ON DBO.KL_Key_LastGen
REFERENCING NEW AS NEWROW
FOR EACH ROW MODE DB2SQL
set newrow.KL_Edit_When = current timestamp, KL_Edit_Who = USER
@

-- test

db2 -td@ -f dd.sql
db2 "select * from KL_Key_LastGen"

KL_EDIT_WHO KL_EDIT_WHEN XXX
-------------------------------------------------- -------------------------- -----------

0 record(s) selected.

[jon@lillebror database]$ db2 "insert into KL_Key_LastGen (xxx) values 1"
DB20000I The SQL command completed successfully.
[jon@lillebror database]$ db2 "select * from KL_Key_LastGen"

KL_EDIT_WHO KL_EDIT_WHEN XXX
-------------------------------------------------- -------------------------- -----------
JON 2003-08-20-15.47.45.490237 1

1 record(s) selected.

You will probably want to capture updates on the table, the same way. I.e. by creating triggers like

NO CASCADE BEFORE UPDATE ON DBO.KL_Key_LastGen

as well


HTH
/Lennart

Sorry, forgot the explanation. The reason that you dont discover any action from the trigger, might depend on the fact that the rows that match the trigger condition have not yet been commited. I.e. if you construct a trigger as:

CREATE TRIGGER TEST
AFTER INSERT ON XX
REFERENCING NEW AS NEWROW
FOR EACH ROW MODE DB2SQL
UPDATE XX SET T = CURRENT TIMESTAMP
WHERE X = NEWROW.X

and then insert into an empty table

insert into xx (x) values 1

At the time of update the newrow is not yet commited, hence there are no rows in the tables that match X = NEWROW.X

Normally one uses BEFORE triggers for this type of thing. Another thing where one uses BEFORE triggers is for CONSTRAINT things (which are not possible to express as table constraints. Example:

CREATE TRIGGER CHK_WHATEVER
NO CASCADE BEFORE UPDATE ON XX
REFERENCING OLD AS OLDROW NEW AS NEWROW
FOR EACH ROW MODE DB2SQL
WHEN (
oldrow.status = 3 and newrow.status NOT IN (4, 5)
) SIGNAL SQLSTATE '75000' ('Message goes here')@


HTH
/Lennart

It is listed as valid.

Thanks for answering....
It still doesn't work....
I'm thinking there has to be somethingelse wrong.
Could it be trying to compile the triggers like it does with
SQL language stored procedures ???
We're having problems with that as well since we don't have a compiler or it's somehow not been installed ???

On a separate note.... Do we have to purchase the compiler separate ???
Thanks.
JR

>Thanks for answering....
>It still doesn't work....

I take it that you have changed to a before trigger and it still doesnt update those columns. What happens if you try it in a dummie schema. I.e.

CREATE TABLE Dummie.KL_Key_LastGen (
KL_Edit_Who varchar(50),
KL_Edit_When timestamp,
xxx int
)@


CREATE TRIGGER Dummie.KL_Key_LastGen_Upd
NO CASCADE BEFORE INSERT ON Dummie.KL_Key_LastGen
REFERENCING NEW AS NEWROW
FOR EACH ROW MODE DB2SQL
set newrow.KL_Edit_When = current timestamp, KL_Edit_Who = USER

and then insert a row like I did. Do you get the expected result or does it still not work?

>I'm thinking there has to be somethingelse wrong.
>Could it be trying to compile the triggers like it does with
>SQL language stored procedures ???

That should not be the case

>We're having problems with that as well since we don't have a >compiler or it's somehow not been installed ???
>
>On a separate note.... Do we have to purchase the compiler >separate ???

I dont think that there is a compiler shipped with DB2. I didnt see what platform your on, but gcc is availible for free on most platforms

/Lennart


>Thanks.
>JR

That works with the dummy table.
But still not with the real one ???
JR

Needs to be BEFORE INSERT trigger, like the test example you tried.

It is defined as

Oops....

It is defined as :

CREATE TRIGGER DBO.KT_Key_Table_Upd
NO CASCADE BEFORE INSERT ON DBO.KT_Key_Table
REFERENCING NEW AS NEWROW
FOR EACH ROW MODE DB2SQL
set newrow.KT_Edit_When = current timestamp, newrow.KT_Edit_Who = USER

It doesn't work on the existing table, but it works just fine on
a new one.. ???
That's strange...
Is there a way to make sure there is no trigger besides "DROP TRIGGER xxxx" ???
Thanks
JR

Not sure what you mean. To investigate if there are triggers, you can select from syscat.triggers. I.e.

select * from syscat.triggers where trigname = 'KT_Key_Table_Upd'

But inorder to get rid of a trigger you use drop. I.e.

drop trigger DBO.KT_Key_Table_Upd

BTW, could you post ddl for the table and trigger?


/Lennart

Here are the Definitions:

-- SELECT * FROM SYSCAT.TRIGGERS
-- * DROP TABLE DBO.KL_Key_LastGen
CREATE TABLE DBO.KL_Key_LastGen (
KL_Table_Key VARCHAR(18) NOT NULL,
KL_Last_NKey INT DEFAULT 1 NOT NULL,
KL_Last_CKey VARCHAR(20) DEFAULT '1' NOT NULL,
KL_DayStamp SMALLINT DEFAULT 0 NOT NULL,
KL_Edit_Who VarChar(30) DEFAULT USER,
KL_Edit_When TIMESTAMP DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_KL_Key_LastGen PRIMARY KEY (KL_Table_Key)
)
-- DROP TRIGGER DBO.KL_Key_LastGen_Upd
CREATE TRIGGER DBO.KL_Key_LastGen_Upd
NO CASCADE BEFORE INSERT ON DBO.KL_Key_LastGen
REFERENCING NEW AS NEWROW
FOR EACH ROW MODE DB2SQL
set newrow.KL_Edit_When = current timestamp, KL_Edit_Who = USER