LISTING 1: Script to Create Triggers for All Tables

if exists (select * from sysobjects where id = object_id('dbo.crt_dbg_trg') and sysstat & 0xf = 4)
	drop procedure dbo.crt_dbg_trg
GO

create procedure crt_dbg_trg as
	declare @tbl_name varchar(50), @db_name varchar(30), @CRLF  char(2)
	declare @sql varchar(255), @sql_2 varchar(255), @sql_3 varchar(255)
	-- for SQL 6.5, varchar can have at most 255 bytes
	declare curTblName cursor for
		select name from sysobjects where type='U'
	select @db_name = db_name()
	select @CRLF = char(13) + char(10)
	open curTblName
	fetch next from curTblName into @tbl_name	
 	while (@@fetch_status <> -1)
	  begin
		if (@@fetch_status <> -2)
		  begin
			select @sql=""
			select @sql =  "create trigger " + "trg" + RTRIM(upper(@tbl_name))
			select @sql= @sql + " on " + rtrim(@tbl_name)
			select @sql= @sql + " for update, insert, delete as "
			select @sql= @sql + " declare @icnt int, @dcnt int, @stat char(1) " + @CRLF
			select @sql= @sql + " select @icnt=count(*) from inserted " + @CRLF
			select @sql= @sql + " select @dcnt=count(*) from deleted" + @CRLF

			select @sql_2=" "
			select @sql_2= @sql_2 + "if (@icnt > 0) and (@dcnt>0) select @stat='U'" + @CRLF
			select @sql_2= @sql_2 + " else if @icnt>0 select @stat='I'" + @CRLF
			select @sql_2= @sql_2 + " else select @stat='D'" + @CRLF

			select @sql_3=" "
			select @sql_3= @sql_3 + "insert into dbg_db.dbo.dbg_info values(db_name(),'" + rtrim(@tbl_name) + "', @stat, DEFAULT)" + @CRLF
			select @sql_3= @sql_3 + "if @icnt>0 insert into " + @db_name +"_BK.." + rtrim(@tbl_name)  + " select * from inserted" + @CRLF
			select @sql_3= @sql_3 + "if @dcnt>0 insert into " + @db_name +"_BK.." + rtrim(@tbl_name)  +  " select * from deleted" + @CRLF

			 exec (@sql + @sql_2 + @sql_3) 
		  end
		fetch next from curTblName into @tbl_name
	  end
	print " "
	print "***************** End *****************"
	close curTblName
	deallocate curTblName