By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,611 Members | 2,259 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,611 IT Pros & Developers. It's quick & easy.

Replacing a function referenced in a view

P: n/a
I have several user defined functions which get called from triggers.
Is it possible to install a new version of the functions without
having to drop and recreate the triggers? When I try to recreate the
function (by DROPping and CREATEing), I get the error

SQL0478N DROP or REVOKE on object type "FUNCTION" cannot be processed
because there is an object "MY_TRIGGER", of type "VIEW or SUMMARY
TABLE",
which depends on it. SQLSTATE=42893

Thanks for any advice.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
I don't think I'd want to do that. If I used a function in a trigger to
populate a column which is indexed; then changing the function could
easily change its output which would invalidate the index contents.
Dropping the trigger wouldn't fix the index but would hopefully give the
DBA a clue that more issues can arise from the function change.

Phil Sherman
Bruce wrote:
I have several user defined functions which get called from triggers.
Is it possible to install a new version of the functions without
having to drop and recreate the triggers? When I try to recreate the
function (by DROPping and CREATEing), I get the error

SQL0478N DROP or REVOKE on object type "FUNCTION" cannot be processed
because there is an object "MY_TRIGGER", of type "VIEW or SUMMARY
TABLE",
which depends on it. SQLSTATE=42893

Thanks for any advice.


Nov 12 '05 #2

P: n/a
Bruce,

Teh answer today is: No.
You will need to drop the trigger, change your function and then
recreate it.
In DB2 V8.2 you can try DB2LK_DEP_OF() to tell you objects dependent on
the one to change.

Cheers
Serge
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.