DB2 v9.5 Trigger Question

[David Robertson]

Hello,

I have a request that I thought was straightforward and would be fairly simple. After many hours of research and trying various triggers, procedures and variables, I have not been able to find a solution.

I need to evaluate a timestamp column to be in an acceptable range on insert/update of a table. If the value is out of the acceptable range, I need to update it to an acceptable value and then write a record into an audit table recording the change.

I began with a BEFORE INSERT trigger, added a WHEN condition and SET the value, no problem, but I cannot insert a new record into another table in a BEFORE trigger. So, I wrote 2 triggers, SET the timestamp in the BEFORE trigger, and INSERT the audit record in the AFTER trigger. This does not work because the WHEN condition in the AFTER trigger fails because the BEFORE trigger has modified the value so I do not know when I need to write the audit record.

I then tried moving the INSERT into a DB2 procedure that I called from the BEFORE trigger, but I learned you cannot call a procedure with 'MODIFIES SQL DATA' from a BEFORE trigger.

Back to the drawing board,my latest attempt is creating a GLOBAL VARIABLE that is SET in the BEFORE trigger that I can then evaluate in the AFTER trigger WHEN condition to know when to write the audit record. I have not been able to SET the GLOBAL VARIABLE in the BEFORE trigger, I get a -20430 SQLCODE "A global variable cannot be set or applied in this context."

I am running out of ideas on how to accomplish this. Conceptually it seems to be simple.

Here is the latest trigger / global variable statement I have:

CREATE VARIABLE gv_BLOCK_EFFDATE_ADJUST_DATE TIMESTAMP
DEFAULT CURRENT TIMESTAMP;

CREATE TRIGGER "BLOCK_BI_EFFDATE_ADJ"
NO CASCADE BEFORE INSERT
ON ADJ
REFERENCING
NEW AS "N"
FOR EACH ROW
WHEN (N.EFF_DATE < (SELECT MIN(E.EFF_DATE) FROM "DATE_CUTOFF" )
BEGIN ATOMIC
SET gv_BLOCK_EFFDATE_ADJ_DATE = N.EFFDATE;
SET (N.EFF_DATE) = (SELECT MIN(E.EFF_DATE)
FROM "DATE_CUTOFF" );
END;

CREATE TRIGGER "BLOCK_AI_EFFDATE_ADJ"
AFTER INSERT
ON ADJ
REFERENCING
NEW AS "N"
FOR EACH ROW
WHEN (gv_BLOCK_EFFDATE_ADJ_DATE < (SELECT MIN
(E.EFF_DATE) FROM "DATE_CUTOFF" ))
BEGIN ATOMIC
INSERT INTO NOTES
(RECORD_DATE,USER_ID,ROWID,COMMENT,TRANMEMO)
VALUES( CURRENT TIMESTAMP,USER_ID,NEXTVAL FOR
SEQ_NOTES),'SYSTEM MODIFIED EFFDATE', ' THE
SYSTEM MODIFIED THE EFFECTIVE DATE TO BE EQUAL
TO THE INCEPTION DATE.');
END;

COMMIT;


Any advice or ideas will be greatly appreciated!!