Before update trigger: Trigger is defined with an unsupported triggered SQL statement

grofaty · #1 (**permalink**) 11-10-08, 03:38

Hi,
on DB2 v8.2 FP9 on Windows one of applications corrupts data. I know this happens with update statemet. I would like to:
1. write a trigger to get data before update and write data values to new table and
2. write a trigger to get data after update and write data in new table

For step 2. I have writtne trigger:

Code:

CREATE TRIGGER ADMIN.TRIG1
AFTER UPDATE OF COL1, COL2 ON ADMIN.TAB
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN ATOMIC
     INSERT INTO ADMIN.NEW_TABLE VALUES (NEW.COL1, NEW.COL2);
END 
@

Above trigger works fine. Now I would like to write the same but 'before update' somethink like bellow:

Code:

CREATE TRIGGER ADMIN.TRIG1
BEFORE UPDATE OF COL1, COL2 ON ADMIN.TAB
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN ATOMIC
     INSERT INTO ADMIN.NEW_TABLE VALUES (NEW.COL1, NEW.COL2);
END 
@

but I get error:
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 "ADMIN.TRIG1" is defined with an unsupported triggered SQL statement. LINE NUMBER=8. SQLSTATE=42987

Any idea how can I write data to new table to get data from original table before update?

Regards,
Grofaty

JAYANTA_DATTA · #2 (**permalink**) 11-10-08, 05:12

for the failure case, just curious, shouldn't it be "REFERENCING OLD AS OLD"

grofaty · #3 (**permalink**) 11-10-08, 05:19

@JAYANTA_DATTA, I forgot to write that I have also tried the following but getting the same error:

Code:

CREATE TRIGGER ADMIN.TRIG1
BEFORE UPDATE OF COL1, COL2 ON ADMIN.TAB
REFERENCING OLD AS OLD
FOR EACH ROW
BEGIN ATOMIC
     INSERT INTO ADMIN.NEW_TABLE VALUES (OLD.COL1, OLD.COL2);
END 
@

JAYANTA_DATTA · #4 (**permalink**) 11-10-08, 06:30

Hi Grofaty,
May be this note can have the answer:

"A BEFORE trigger is run before the row is changed. It is typically used to change the values being entered (e.g. set a field to the current date), or to flag an error. It cannot be used to initiate changes in other tables."

grofaty · #5 (**permalink**) 11-10-08, 07:04

Hi,
thanks. That is exactly what I was wondering. It looks like this is some kind of DB2 trigger limitation.
Thanks

umayer · #6 (**permalink**) 11-10-08, 07:05

A "before" trigger can't modify tables.
INSERT, DELETE and UPDATE are not allowed for an "before" trigger

stolze · #7 (**permalink**) 11-11-08, 10:29

That is not a limitation at all in your case. You can do the logging in the AFTER trigger. You have access to the before and after images via REFERENCES OLD AS ... NEW AS ...

grofaty · #8 (**permalink**) 11-12-08, 04:58

@Stolze, thanks a lot. It after update trigger with references new and old is working as expected.

Internet Services

Web Development

Digital Marketing

Consumer Tech