Using DROP and REVOKE
Care must be taken if database objects are dropped with the
CASCADEoption, and when
REVOKEis performed, particularly with respect to routines and modules.
It is important to bear in mind the following points in connection with modules and routines:
- Dropping an object referenced by an SQL statement in a routine will cause the routine to be dropped.
- If the access rights on a database object are revoked from the creator of a routine that contains an SQL statement referencing the object, the routine will be dropped.
- If a routine belonging to a module is dropped because of the effects of a cascade, the routine is effectively removed from the module (i.e. the module is not dropped).
If an ident attempts to drop a routine for which there is a compiled version currently being held by another ident, the
DROPoperation will fail because the routine is in use.
When a routine is invoked, it is compiled and the compiled version of the routine is held by the invoking ident. Any other idents invoking a routine while a compiled version of it exists will use the existing compiled version and this will be held by them as well.
A compiled version of a routine will generally be held by an ident until the ident disconnects. If the routine invocation is contained in a dynamic SQL statement, deallocating the statement will release the compiled version of the routine immediately without the need for a disconnect.
Mimer Information Technology AB
Phone: +46 18 780 92 00