When you change the lock scheme of a table from allpages to datapages/datarows, or vice versa,
all triggers for the table should be dropped and recreated afterwards (this is to avoid potential trigger malfunction due to the different page/row layout).
In this situation, the DBA usually runs a script with the source code for the trigger with isql, sqsh, defncopy or some other client utility.
However, there is an easier way to do this. The stored procedure sp_recreate will recreate a trigger from its own SQL source code (as stored in syscomments). This operation is instantaneous, and since it is a stored procedure, it can be performed simply inside ASE.
Apart from the practical usefulness, sp_recreate combines some interesting applications of SQL features, like execute-immediate. Interested readers are invited to check out the source code...
Installation
To install the stored procedure, follow these steps:
- Execute this script with isql, using a login having sa_role and sso_role.
Example
-- recreate the trigger 'invoices_trig_ins'
sp_recreate invoices_trig_ins
-- recreate the trigger 'jsmith.orders_trig_upd'
-- and print the SQL text
sp_recreate 'jsmith.orders_trig_upd', 1
-- display usage info
sp_recreate
Notes
Apart from the regular disclaimers, please note the following:
- sp_recreate relies heavily on the combination of execute-immediate and 16Kb run-time string expressions; therefore it can only be used in ASE 12.5 or later.
- sp_recreate uses the compiled object's SQL source text as stored in syscomments; in case this text is hidden with sp_hidetext, sp_recreate won't work.
- sp_recreate cannot handle objects whose source text is longer than 16384 bytes.
- When an object has been created with the name 'x' and is then renamed to 'y', the SQL source code in syscomments still contains the original name. Hence, sp_recreate will recreate the object as 'x' but will also raise an error since the resulting object name is not as expected.
- Objects relying on the existence of #temporary tables cannot be recreated by sp_recreate. You'll need to re-run the original object creation script.
- sp_recreate requires the set proxy command has been granted to sso_role for objects owned by a different user than the user executing sp_recreate.
- Although the same approach could in principle be used for recreating other types of compiled objects, sp_recreate currently handles only triggers.
- Be aware of the fact that, since ASE 12.0, select * in compiled objects is expanded to the full column list. Traceflag 243 stops this expansion. For more information, go here.
- See the header of the downloaded file for additional usage notes.
Revision history
- Version 1.1 - 25-Mar-2004 - Use a more robust algorithm to concatenate syscomment.text
- Version 1.0 - 04-Mar-2004 - First version
|
