Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Can we update same Table in a Trigger ?

    Hi guys,


    The BEFORE trigger does not allow to use INSERT, UPDATE, DELETE inside.

    And AFTER trigger does not have NO CASCADE options so if I update the same table inside I will reach the max level allowed and get the error.
    This is the coment of one of my team member.

    Team wants me to find a way to Update the columns of the table inside the trigger so that it is posible to mask some changes or format some values.

    And even to deny changes they want to find a way so that the trigger can reverse changes quietly

    I am working on this and need help to get this accomplished some how.

    Thanks
    DBFinder

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Can you give us a more specific question? What is it exactly that you want to accomplish? Also please provide you DB2 and OS version.

    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although it is neccesary to know more specific requirements as Andy wrote,
    I thought that generated column and/or check constraint might be an alternative for some requirements. Like...
    create view with hiding original column, revoke access on original table and grant access on view to needed users.

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    If you want to update columns in the same row, in a BEFORE trigger you may use NEW transition variable.

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Actuall here is what our programmer want to do.

    Table TRANS recieves a transaction via update or insert.
    based on this transaction application another table taking data from TRANS table.
    So that another table is ACH_TRANS. Now when application tries to update this table the trigger on this table (ACH_TRANS_UPDT) gets activated.

    The programmer wants the trigger scan for some timestamp field .. say .. TMSTAMP to compare with today's date and redirect the update on the same table. And he says if NEW.TMSTMP are so and so he wants to stop the update and cause to rollback the update on table ACH_TRANS.

    Other situation is that he wants to update same (ACH_TRANS) table with OLD values. Means no update has happend and no error or exception was signalled.

    I tried few ways but did not suceeed yet.

    The versions are 8.2.5 and 9.1 - on windows 2k3 servers - means two different production servers with exactly identical database table structures.

    Any help is badly needed.

    Thanks
    DBFinder
    Last edited by DBFinder; 04-23-09 at 12:30.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by DBFinder
    Actuall here is what our programmer want to do.

    Table TRANS recieves a transaction via update or insert.
    based on this transaction application another table taking data from TRANS table.
    So that another table is ACH_TRANS. Now when application tries to update this table the trigger on this table (ACH_TRANS_UPDT) gets activated.

    The programmer wants the trigger scan for some timestamp field .. say .. TMSTAMP to compare with today's date and redirect the update on the same table. And he says if NEW.TMSTMP are so and so he wants to stop the update and cause to rollback the update on table ACH_TRANS.
    Use a before update trigger. If the condition is met, do a SIGNAL. The application will have to do the rollback.

    Quote Originally Posted by DBFinder
    Other situation is that he wants to update same (ACH_TRANS) table with OLD values. Means no update has happend and no error or exception was signalled.
    For this one, do another BEFORE update trigger. If the condition is met, set the new values to the old values.

    Andy

  7. #7
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thanks ARWinner,

    You are really winner. This worked beautifully.

    Same Programmer is asking for SP for same bussiness logic.

    How does a trigger compare with SP , performance wise ??

    Thanks again,
    DBFinder

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by DBFinder
    Thanks ARWinner,

    You are really winner. This worked beautifully.

    Same Programmer is asking for SP for same bussiness logic.

    How does a trigger compare with SP , performance wise ??

    Thanks again,
    DBFinder
    About the same. You should see no difference in performance between the two.

    Andy

Posting Permissions

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