Hi,

I am new to DB2. Following is the trigger code I tried to execute in z/OS mainframe system.

CREATE OR REPLACE TRIGGER TUBR01_POC1
BEFORE INSERT OR UPDATE ON TABLE POC1
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
N.POC_COL2 = CURRENT TIMESTAMP;
---------+---------+---------+---------+---------+---------+---------+--------

I am getting the following error.

DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD TRIGGER, TOKEN FOR WAS
EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 19 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
X'00000013' X'00000000' SQL DIAGNOSTIC INFORMATION

Please throw some light on this error. I really appreciate your help

Thanks
Santhi

Hello Santhi,
you only can create or drop a trigger and there is no replace command avail for trigger. You also can create a update or insert trigger not both at once.

Hope this will help

Marc

Hi Santhi,

marc again.

a little example:

create trigger (triggername)
no cascade before (activation time)
update (trigger event)
of (columnname) on (tablename)
referencing old as old
new as new
for each row (granularity)
mode db2sql (shit needed by db2)
when (trigger condition) condition
begin atomic (trigger action ends with ';')
end@ (you must alter the sql terminator to a different sign than ';'
for example '@')

This is only an example !

regards

marc

Hi,

Thanks for your immediate reply.

I modified the trigger as below.

CREATE TRIGGER TUB_POC1
NO CASCADE
BEFORE INSERT
ON TABLE POC1
REFERENCING NEW AS N
FOR EACH ROW
MODE DB2SQL
N.POC_COL2 = CURRENT TIMESTAMP;


I am getting following error now.

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "REFERENCING". SOME SYMBOLS
THAT MIGHT BE LEGAL ARE: OLD, NEW, OLD_TABLE, NEW_TABLE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHSM5R SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 289 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'

What we are trying to do is update a timestamp field before inserting any new row. Can we update the same table data on update trigger?
Thanks
santhi

Hi,

CREATE TRIGGER TUB_POC1
NO CASCADE
BEFORE INSERT
ON POC1
REFERENCING NEW AS N
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
SET N.POC_COL2 = CURRENT TIMESTAMP
WHERE (enter search condition for the explicit row to be updated);
END@

It's import to alter the sql terminator to the character used after the
keyword end. If u don't the statement ends after the ';' but this is not the end of the create trigger statement !!!

In this example u are updating the same table the trigger is defined on.
If u wanna update another table u have to write a update statement for that within the begin atomic/end step.

regards

marc

Hi Mark.

Do I have to give explicit where condition to update the current record. I guess N represents the current row that is going to be created in this case. so when I say N.column it refers to single row. So I guess where condition is not required in my scenario. Please confirm this.

I really appreciate your help. The problem is I dont have access to execute this trigger, I have to give it to DBA everytime I change for execution.

Thanks alot once again
Santhi

Hi,

i think u are rught, my fault !!!

regards

marc