Sybase ASE
11.0 11.5 11.9 12.0 12.5 15.x
Last updated: 25 March 2007
Recreating triggers with 'sp_recreate'
 
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


 
 This document is located at www.sypron.nl/recreate.html