/*
 * SP_RECREATE.SQL - recreate a trigger in a single command
 * 
 * Description
 * ===========
 * This file creates 'sp_recreate', a stored procedure for Sybase ASE to 
 * recreate a trigger without having to re-enter the full SQL source code.
 * This file can be downloaded from www.sypron.nl/recreate.html .
 * 
 * Usage
 * =====
 *   sp_recreate trigger_name [, 1 ]
 *   ==> this drops and recreates the specified trigger. When specifying
 *       any numeric value for the optional second parameter, the SQL source 
 *       code is displayed as well.
 *
 *   Recreating a trigger is not usually needed, but a common situation is
 *   when changing the lock scheme of a table to or from 'allpages'. In this
 *   triggers on that table should be dropped and recreated to avoid problems
 *   that can cause the triggers to malfunction. Instead of re-entering the 
 *   full SQL source code for the trigger, or running a SQL script, 'sp_recreate'
 *   does the job in a single command: it extracts the source code from 
 *   syscomments and recreates the trigger through exec-immediate. 
 *   
 * Notes
 * =====
 * - sp_recreate cannot recreate triggers whose source code has been 
 *   hidden with sp_hidetext
 *
 * - sp_recreate cannot recreate triggers that are currently disabled (well, 
 *   it can, but that would lead to a short period when the trigger would 
 *   be enabled, and that's perhaps best avoided if the trigger was disabled 
 *   in the first place).
 *
 * - sp_recreate cannot handle objects where the SQL source code exceeds 
 *   16384 bytes
 *
 * - Make sure no DML activity takes place on the table when running sp_recreate
 *
 * - 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.
 *
 * - To avoid security issues, an object is recreated under the same login as by
 *   which it was originally created. This requires that the 'set proxy' command 
 *   has been granted to the login executing sp_recreate (or rather, to 'sso_role').
 *
 * - 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.
 *   This is checked by the procedure itself.
 *
 * - Objects relying on the existence of #temporary tables cannot be recreated 
 *   by sp_recreate.
 *
 * - Note that not all ownership situations can be recreated. For example, if a 
 *   login is a member of the 'guest' group and creates an object, and is then
 *   added to the database with its own user ID, sp_recreate won't be able to
 *   recreate the object originally created with 'guest' ownership 9but neither 
 *   will that user itself be able to do the same).
 *
 * - 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 to www.sypron.nl/new12501.html .
 *
 * - Currently, sp_recreate can handle only triggers.
 *
 *
 * Requirements
 * ============
 * To execute sp_recreate, "sa_role" is required. 
 * To display help info, execute the procedure with "?" as the only parameter. 
 * 
 * This procedure requires ASE version 12.5 or later, since it relies on
 * the 12.5 feature of 16Kb run-time string expressions.
 *
 *
 * Installation
 * ============
 * Execute this script using "isql", using a login having both "sa_role"
 * and "sso_role".
 * The stored procedure will be created in the sybsystemprocs database.
 *
 * 
 * Revision History
 * ================
 * Version 1.0  04-Mar-2004  Version 1.0
 * Version 1.1  25-Mar-2007  Version 1.1: changed algorithm to 
 *		             concatenate syscomments.text.
 * 
 *
 * Copyright Note & Disclaimer :
 * =============================
 * This software is provided "as is"; there is no warranty of any kind.
 * While this software is believed to work accurately, it may not work 
 * correctly and/or reliably in a production environment. In no event shall  
 * Rob Verschoor and/or Sypron B.V. be liable for any damages resulting 
 * from the use of this software. 
 * You are allowed to use this software free of charge for your own 
 * professional, non-commercial purposes. 
 * You are not allowed to sell or bundle this software or use it for any 
 * other commercial purpose without prior written permission from 
 * Rob Verschoor/Sypron B.V.
 * You may (re)distribute only unaltered copies of this software, which 
 * must include this copyright note, as well as the copyright note in 
 * the header of each stored procedure.
 *
 * Note: All trademarks are acknowledged.
 *
 * Please send any comments, bugs, suggestions etc. to the below email
 * address.
 *
 * (c) 2004-2007 Copyright Rob Verschoor/Sypron B.V.
 *                    P.O.Box 10695
 *                    2501 HR Den Haag
 *                    The Netherlands
 *
 *                    Email: rob@sypron.nl
 *                    WWW  : http://www.sypron.nl/
 *---------------------------------------------------------------------------- 
 */

set nocount on
go
set flushmessage on
go
print ""
print "Installing 'sp_recreate' v.1.1 ..."
print ""
go
use master
go
if charindex("sa_role", show_role()) = 0
or charindex("sso_role", show_role()) = 0
begin
   print ""
   print ""
   print "***************************************"
   print "***************************************"
   print " You need 'sa_role' and 'sso_role' "
   print " to run this script."
   print " Please retry with these roles enabled."
   print " Aborting..."
   print "***************************************"
   print "***************************************"
   print " "
   print " "
   print " "
   print ""
   set background on  -- terminate this script now
end
go

--
-- check we're at version 12.5 or later
--
if isnull(object_id("master.dbo.syscertificates"),99) >= 99
begin
   print ""
   print ""
   print "***************************************"
   print "***************************************"
   print " This procedure requires ASE 12.5 or"
   print " later."
   print " Aborting..."
   print "***************************************"
   print "***************************************"
   print " "
   print " "
   print ""
   set background on  -- terminate this script now
end
go

use sybsystemprocs
go

sp_configure "allow updates", 1
go

if object_id("sp_recreate") <> NULL
begin
    drop procedure sp_recreate
end
go

create proc sp_recreate
/* Copyright (c) 2004 Rob Verschoor/Sypron B.V. */
   @objname varchar(80) = NULL,  -- the object name
   @print_sql int = NULL         -- specify any value to print the SQL text
as
begin

set nocount on

declare @cmd varchar(16384)
declare @cmd2 varchar(100), @type_full varchar(15), @unique_name varchar(80)
declare @id int, @id_new int, @err int, @rc int, @maxlen int, @totlen int, @nr_lines int
declare @type char(2), @fill varchar(9)
declare @trigtype varchar(30), @trigmask int, @tabid int, @tabname varchar(80)
declare @site varchar(30), @dbname varchar(30), @uname varchar(30), @procname varchar(30)
declare @objname_full varchar(120), @objname2 varchar(30)
declare @this_login varchar(30), @loginame varchar(30)
declare @txt varchar(255)

-- init
select @objname_full = @objname
select @this_login = suser_name()

-- max length of a string in ASE 12.5+
select @maxlen = 16384

-- usage info
if @objname = NULL or @objname = "?"
begin
    print "Usage:"   
    print "   sp_recreate trigger_name [, 1 ]"   
    print "   * Note: when specifying any numeric value for the second"
    print "     parameter, the SQL source code is displayed as well."   
    print " " 
    print "   Version 1.1 (March 2007)"
    return -1
end

-- Check executing user has sa_role and sso_role
if proc_role("sa_role") = 0 or  proc_role("sso_role") = 0
begin
    print "You need 'sa_role' and 'sa_role' to run this procedure"
    return -1
end

-- thanks for Sybase's sp_namecrack!
select @site = null, @dbname = null, @uname = null, @procname = null
exec sp_namecrack @objname, @site output, @dbname output, @uname output, @objname2 output
if @uname = NULL
  select @uname = user_name()

select @objname_full = @uname + '.' + @objname2

-- some checks
if @objname like "%.%.%"
begin
    print "Object must be in the current database."
    return -1
end

-- find the object
select @id = id, @type = type, @loginame = loginame
from dbo.sysobjects
where name = @objname2
  and  uid = user_id(@uname)

select @err = @@error, @rc = @@rowcount

if @rc <= 0
begin
   select name, uid, type
   into #objs 
   from dbo.sysobjects
   where name = @objname2
     and type = "TR"
   if @@rowcount > 0
   begin
      print "Object '%1!' not found", @objname_full
      print "Maybe you should specify the owner? The following objects have a similar name:"
      select user_name(uid) + "." + name "owner.name" from #objs order by 1
   end
   else
      print "Object '%1!' not found", @objname_full

   return -1
end

if @rc > 1
begin
   print "Oops... object '%1!' exists %3! times -- gotta fix this code !", @objname_full, @rc
   return -1
end

--
-- determine the object type
--
if @type not in ("TR")
begin
   print "Currently, 'sp_recreate' can only recreate triggers."
   print "Object '%1!' has sysobjects.type = '%2!'", @objname_full, @type
   return -1
end

select @type_full = 
          case @type 
	     when "P" then "procedure"
	     when "TR" then "trigger"
	     when "V" then "view"
	     else "????"
	  end

-- if the object is owned by a different user, check 'set proxy' has been granted
if isnull(@loginame, suser_name()) <> suser_name() 
begin
if not exists (select * from 
           master.dbo.sysprotects p, 
	   master.dbo.sysroles r, 
	   master.dbo.syssrvroles rv 
	   where p.action=167 
	     and p.uid=r.lrid 
	     and r.id=rv.srid
	     and rv.name = 'sso_role')
begin
   print " "
   print "You must first execute the following command (in the 'master' databse):"
   print "  grant set proxy to sso_role "
   print " "
   print "Then, re-run 'sp_recreate'."
   return -1
end
end

print "Object '%1!' is a %2!; object id=%3!", @objname_full, @type_full, @id

-- figure out the type of trigger and the parent table
if @type in ("TR")
begin
select @trigtype = "", @trigmask = 0

select @trigtype = "insert", @tabid = id, @trigmask = @trigmask + 1048676
from dbo.sysobjects 
where instrig = @id

if @@rowcount > 0 select @trigtype = @trigtype + "+"

select @trigtype = @trigtype + "update", @tabid = id, @trigmask = @trigmask + 4194304
from dbo.sysobjects 
where updtrig = @id

if @trigtype <> "" and @@rowcount > 0 select @trigtype = @trigtype + "+"

select @trigtype = @trigtype + "delete", @tabid = id, @trigmask = @trigmask + 2097152 
from dbo.sysobjects 
where deltrig = @id

select @tabname = object_name(@tabid)

select @trigtype = ltrim(@trigtype)
if right(@trigtype,1) = "+" select @trigtype= substring(@trigtype,1,char_length(@trigtype)-1)

if @trigtype = "" or @tabid = NULL
print "Error locating trigger's parent table... Something seems wrong here..."
else
print "This is an %1! trigger on table '%2!'", @trigtype, @tabname
end


-- Check the trigger is not disabled. Although we can simple replace it and disabled it again, 
-- that would leave the newly created trigger enabled for a short time. To avoid unexpected 
-- side effects, let's tell the user about it; he's got to enable the trigger first. 
if @type in ("TR")
begin
   if (select sysstat2 & @trigmask
       from dbo.sysobjects 
       where id = @tabid) <> 0
   begin
      print "Trigger '%1!' is currently disabled. You must enable the trigger", @objname
      print "before recreating it with 'sp_recreate'."
      return -1
   end
end

--
-- Check if source code is hidden or deleted
--
if (select distinct status & 1 
    from dbo.syscomments
    where id = @id) = 1
begin
    print "The T-SQL source code of this object was hidden (with 'sp_hidetext')"
    print "Therefore this object cannot be recreated with 'sp_recreate'..."
    return -1
end

--
-- We cannot handle grouped procs right now...
--
if (select count(distinct number) 
    from dbo.syscomments
    where id = @id) > 1
begin
    print "'sp_recreate' can't handle grouped stored procedures at the moment; sorry..."
    return -1
end

-- Check max. length of source. 
select @totlen = sum(datalength(text)),
       @nr_lines = count(id)
from dbo.syscomments
where id = @id

if @totlen = NULL or @totlen = 0
begin
   print "The T-SQL source text for this object seems to have been deleted"
   print "Therefore this object cannot be recreated using 'sp_recreate'"
   return -1
end

if @totlen > @maxlen
begin
   print "The T-SQL source text for this object is too long (%1! bytes; max. is %2!)", @totlen, @maxlen
   return -1
end

--
-- Concatenate the source text into a string variable using with a cursor
--
select @cmd = ' '

declare sc cursor
for
   select text
   from dbo.syscomments
   where id = @id
   order by colid
for read only

open sc

while 1=1
begin
  fetch sc into @txt
  if @@sqlstatus > 0 break

  select @cmd = @cmd + @txt
end

close sc
deallocate cursor sc

--
-- print source text if desired
--
select @fill = replicate('*', char_length(convert(varchar,@totlen)))

if @print_sql != NULL
begin
   print ""
   print "****** SQL text for object '%1!' (%2! bytes) *******************", @objname_full, @totlen
   print "%1!", @cmd
   print "****** end of SQL text for object '%1!' *********************%2!", @objname_full, @fill
   print ""
end

-- If necessary, assume the object owner's identity
if isnull(@loginame, suser_name()) <> suser_name() 
begin
   set proxy @loginame
   select @err = @@error
   if @err = 10331
   begin
      print " "
      print "You must first execute the following command (in the 'master' databse):"
      print "   grant set proxy to sso_role "
      print " "
      print "Then, re-run 'sp_recreate'."
      return -1
   end
   if @err > 0
   begin
      print "The object has not been recreated."
      return -1
   end
end


-- Recreate the object. Note that we don't need to drop the trigger, since 
-- 'create trigger' does that implicitly.
exec (@cmd)
select @err = @@error
if @err != 0
begin
   print "Error recreating object '%1!'", @objname_full

   if @type != "TR"
   begin
   print "Renaming original object back..."
   exec sp_rename @unique_name, @objname
   end

   if @loginame <> suser_name()
   begin
      set proxy @this_login
   end
   return -1
end

-- Assume own identity again
if isnull(@loginame, suser_name()) <> suser_name() 
begin
   set proxy @this_login
end

--
-- Double check the recreate was successful 
--
select @id_new = object_id(@objname_full)
if @id_new != NULL and object_id(@objname_full) != @id
begin
   print "Object '%1!' (%2!) successfully recreated", @objname_full, @type_full
   print "(new object id=%1!; old id=%2!)", @id_new, @id
end
else
begin
   print "Error recreating object '%1!' ", @objname_full
   print "(something must have gone wrong... maybe the object had been renamed?)"

   -- print the SQL to avoid losing it completely...
   print ""
   print "****** SQL text for object '%1!' (%2! bytes) *******************", @objname, @totlen
   print "%1!", @cmd
   print "****** end of SQL text for object '%1!' *********************%2!", @objname, @fill
   print ""

   return -1
end

return 0
end
go

sp_configure "allow updates", 0
go

--
-- end of file
--