How to recreate trigger in DB2

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?

Who is Participating?
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
you can grant him the alter permission if you don't want it to have the drop permission
AnniABAuthor Commented:
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?

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

AnniABAuthor Commented:
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.
Kent OlsenData Warehouse Architect / DBACommented:
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,
you understood it ok
AnniABAuthor Commented:
Thank you. I'll be going forward with this approach.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.