Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    3

    Unanswered: commit/rollback from trigger

    Hi ,

    Following is the code of trigger written by me.
    ************************************************** **********
    CREATE TRIGGER RAMDASN.TR_INSERT_CHILD
    AFTER INSERT ON RAMDASN.TEST_STAGING_1
    REFERENCING NEW AS o
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    INSERT INTO RAMDASN.TEST_PARENT_1(USERS_ID,STATUS,LOGONID,LOGO NPASSWORD)VALUES (NEXTVAL FOR TEST_SEQ,o.status,o.logonid,o.logonpassword);
    INSERT INTO RAMDASN.TEST_CHILD_1(USERS_ID,NAME,ADDRESSLINE1,AD DRESSLINE2,CITY)VALUES (NEXTVAL FOR TEST_SEQ-1, o.name,o.Addressline1,o.Addressline2,o.city);
    END
    @

    ************************************************** **********
    i have 3 requirments
    1> how do i raise Exception and rollback the operation if one of insert opertaion fails.basically commit/rollback from with in the trigger body.

    2>How can i log the Exception to the log file

    Thanking in advance

    Regards
    Ramdas Nayak

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: commit/rollback from trigger

    I'm not sure, how much worth this is for you ..

    You do not need to explicitly do a commit/rollback in your trigger ... If one of your INSERT Statements fails, the entire UOW is rolled back automatically

    Cheers

    Sathyaram

  3. #3
    Join Date
    Apr 2003
    Location
    Trier, Germany
    Posts
    28

    Re: commit/rollback from trigger

    Hi Sathyaram,

    not the UOW is rolled back, but the triggering action (in this case the first INSERT) and every action since then (triggers and their actions, stored procedures they called...).

    Cheers Ingo


    Originally posted by sathyaram_s
    I'm not sure, how much worth this is for you ..

    You do not need to explicitly do a commit/rollback in your trigger ... If one of your INSERT Statements fails, the entire UOW is rolled back automatically

    Cheers

    Sathyaram

  4. #4
    Join Date
    Apr 2003
    Location
    Trier, Germany
    Posts
    28

    Re: commit/rollback from trigger

    2)
    If you need to do "special logging", you have do external work, e.g. using a stored procedure. But this is difficult, because when an sql error occurs on the insert, the triggering action is stopped immediatly and the triggering action itself is rolled back. So you have to check it before the error occurs. It is easier to check the sql code from within the application, perhaps by wrapping a stored procedure around.

    Cheers Ingo

    Originally posted by ramdas
    Hi ,

    Following is the code of trigger written by me.
    ************************************************** **********
    CREATE TRIGGER RAMDASN.TR_INSERT_CHILD
    AFTER INSERT ON RAMDASN.TEST_STAGING_1
    REFERENCING NEW AS o
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    INSERT INTO RAMDASN.TEST_PARENT_1(USERS_ID,STATUS,LOGONID,LOGO NPASSWORD)VALUES (NEXTVAL FOR TEST_SEQ,o.status,o.logonid,o.logonpassword);
    INSERT INTO RAMDASN.TEST_CHILD_1(USERS_ID,NAME,ADDRESSLINE1,AD DRESSLINE2,CITY)VALUES (NEXTVAL FOR TEST_SEQ-1, o.name,o.Addressline1,o.Addressline2,o.city);
    END
    @

    ************************************************** **********
    i have 3 requirments
    1> how do i raise Exception and rollback the operation if one of insert opertaion fails.basically commit/rollback from with in the trigger body.

    2>How can i log the Exception to the log file

    Thanking in advance

    Regards
    Ramdas Nayak

Posting Permissions

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