Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2006
    Posts
    12

    Unanswered: How to Create an after insert trigger with a call statement in DB2 UDB V8.1 FixPack 7

    Hi,

    Can any body provide some assistance on creating an after trigger in DB2 UDB V8.1 with a call to a Stored Procedure.
    The Stored Procedure modifies SQL data and also the transition variable "NEW" is to be updated in the trigger.

    The trigger code is givenj below:-

    CREATE TRIGGER trgLocChngTranSrl1 AFTER INSERT ON TABMPLOCATIONCHANGE
    REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

    BEGIN ATOMIC

    DECLARE vcNewRowId VARCHAR(40);



    DECLARE nCount FLOAT;



    CALL PRCRECORDIDGENERATION('-1','MN','LocationChange',CAST (NULL AS VARCHAR(1)),
    CAST (NULL AS VARCHAR(1)),CAST (NULL AS VARCHAR(1)),CAST (NULL AS VARCHAR(1)),CAST (NULL AS VARCHAR(1)),vcNewRowId);



    SET NEW.INTTRANSACTIONSERIALNUMBER = ORA.ROUND(ORA.TO_NUMBER(vcNewRowId));

    END!

    The error message is:-

    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 "DB2ADMIN.TRGLOCCHNGTRANSRL1" is defined with an
    unsupported triggered SQL statement. LINE NUMBER=21. SQLSTATE=42987

    Kindly provide the necessary alternatives to create this type of a trigger

    Regards,
    Mahesh.B.S
    Mahy

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    You cannot use the SET - command in an AFTER trigger as the insert is already done

    you must use the UPDATE table statement if you need to change the values just inserted.

Posting Permissions

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