How to recreate trigger in DB2

Posted on 2009-02-11
Last Modified: 2012-05-06
I need to recreate a trigger in db2, if some modifications to its target table structure are done. So far I have done it by dropping and creating the trigger, and it works fine.

However, now I'm facing a situation, where another user, userB, with rights to the target table needs to be able to change the trigger too. userB does not have drop access to the trigger, so can't use the drop/create scheme.

Is there a way for userB to just update the trigger definition and does this require some special grants?

Question by:AnniAB
    LVL 37

    Expert Comment

    you can grant him the alter permission if you don't want it to have the drop permission
    LVL 1

    Author Comment

    Thank you for your answer,

    however, I did not quite follow your suggestion. Do you mean to grant ALTER permission for userB to the trigger? Or to the target table?

    UserB already has ALTER privilege to the target table. I did not find anywhere, how to grant permissions to triggers. How does the grant clause look like?

    LVL 37

    Accepted Solution

    I'm sorry, I had a mistake
    the only way to change a trigger code in db2 is to drop and recreate it
    the only solution that i can see for your problem is creating a procedure that will do the drop and create,
    the procedure will receive the trigger body as a parameter

    that way, the user only gets permissions to execute the procedure and he does not need permissions to drop the trigger
    LVL 1

    Author Comment

    OK just to make this clear: I can create a procedure, which can be executed by a user, who otherwise does not have permissions to do what the stored procedure does? I.e. drop and create a trigger.

    Procedures are a little hazy to me, I did not realize this was possible to do.
    LVL 45

    Expert Comment

    Hi Annie,

    It appears that you're complicating an issue by trying to selectively give "partial" rights to some users.

    The creation of a trigger is a database design issue.  If the architect had the necessary information to design a good database and did a quality job, the need for the trigger was known when the database was built, or with the database changes that now require the trigger.

    Putting a trigger on a table is NOT a duty that you should entrust to an application developer or user.  The performance impact could be significant so the design and incorporation of the trigger should be left to a professional -- your DBA.

    As development shops get larger, it becomes more important for roles to be restricted.  User A may want/believe that the trigger should behave in a particular manner and build the trigger for his own needs.  User B may have a different idea doesn't agree with the needs of User A.  That's where the DBA comes in.  He decides and enforces the database structure and lets the developers do what they do best.

    I suggest that you not entrust the database design issues (like trigger functionality) to too many people.

    Good Luck,
    LVL 37

    Expert Comment

    you understood it ok
    LVL 1

    Author Closing Comment

    Thank you. I'll be going forward with this approach.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now