Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2011
    Location
    Ohio
    Posts
    2

    Unanswered: DB2 Trigger question

    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!!!

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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 ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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)
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jan 2011
    Location
    Ohio
    Posts
    2

    DB2 Trigger Question

    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!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •