Hello,

Sorry for a long post, hopefully I can properly convey my issue.

I have spent many hours searching for a solution to my requirement but I have not found one. I think what I am trying to do is fairly straightforward but I am struggling with the DB2 trigger "rules\limitations".

I need to create a trigger that:

Evaluates a datetime column to be in an acceptable range.
If it is not, modify the value to be within the acceptable range.
If the column is modified by the trigger, write a record into another table to record the change.


I know I can only do a 'SET' in a before insert trigger to modify the column and I can only do an 'INSERT' in an after insert trigger to write the record to the other table to record the change.

I have successfully created both of these triggers with the exact same 'WHEN' condition. The before insert trigger 'WHEN' condition is true and the column is modified, but the after insert trigger 'WHEN' condition is now false since the column is already modified. I do not know when to run the INSERT in the after trigger?

I tried creating a DB2 procedure that would write the new record and call it from the before insert trigger, but I have learned it cannot contain 'MODIFIES SQL DATA'.

So I am now trying to determine how I can evaluate the column in the after insert trigger to know if it was modified by the before insert trigger and only write the new record if it was modified?

Here is the procedure and trigger I have:

CREATE PROCEDURE NOTES_EFFDATE_ADJUST (
IN USERID SMALLINT,
IN RECORDDATE TIMESTAMP)
SPECIFIC "NOTE_EFFDATE_ADJUST"
LANGUAGE SQL
MODIFIES SQL DATA
NOT DETERMINISTIC
NO EXTERNAL ACTION
CALLED ON NULL INPUT
BEGIN
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;


CREATE TRIGGER "BLOCK_BI_EFFDATE_ADJUST"
NO CASCADE BEFORE INSERT
ON ADJUSTMENTS
REFERENCING
NEW AS "N"
FOR EACH ROW
WHEN (N.EFFDATE < (SELECT MIN(E.EFFDATE) FROM "DATE_CUTOFF" )
BEGIN ATOMIC
SET (N.EFFDATE) = (SELECT MIN(E.EFFDATE) FROM "DATE_CUTOFF" );
CALL NOTES_EFFDATE_ADJUST(N.USERID, N.RECORDDATE);
END;

I have thought about "flagging" the record in the before insert trigger and then using the flag in the after insert trigger to know when to write the note record. However the table does not have a suitable column available for such a flag.



Any help is greatly appreciated!!!

An option could be to insert the date as it comes .. Then invoke the AFTER Trigger , check the date, UPDATE the column if necessary and then write the audit record too ..

BTW, what version and platform are you on ?

if you are on 9.5/9.7 you may also consider using GLOBAL VARIABLES in your current (ie, have both before and after trigger)

I am on version 9.5.

Could you provide more specifics on your proposed solution?

An option could be to insert the date as it comes ..

--- Are you suggesting to not have a before trigger and let the date be written in the insert as it comes in?


Then invoke the AFTER Trigger , check the date, UPDATE the column if necessary and then write the audit record too ..

---I did not think I could update the date column in the AFTER trigger, or I am not sure how?

I have tried doing the following but not succesfully
(I was thinking I could not call UPDATE on the trigger table?)

BEGIN ATOMIC

UPDATE EFFDATE SET (EFFDATE) = (SELECT MIN(E.EFFDATE) FROM "DATE_CUTOFF" );

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;


---However, I have been able to successfully create the after trigger to insert the audit record.

I really appreciate the responses and assistance!! Thank You!!