AnniAB
asked on
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?
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?
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Procedures are a little hazy to me, I did not realize this was possible to do.
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,
Kent
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,
Kent
you understood it ok
ASKER
Thank you. I'll be going forward with this approach.
you can grant him the alter permission if you don't want it to have the drop permission