為此我專門寫了兩個存儲過程，適用于sql server 2000，如果您的數據庫不是sql server 2000，也可供您參考 為了新建立的表和觸發器和數據庫中原有的表和觸發器同名，采用了加后綴方法，比如 表名為 users的表，相應的更新表為users+后綴，當后綴為_1234567時，更新表的表名為users_1234567
create procedure p_analysis @postfix char(8) as --測試是否會和數據庫原有的對象名(字段名)重復 if exists(select * from sysobjects where right(name,8)[email protected]) or exists(select * from syscolumns where
right(name,8)[email protected]) print '對象名重復，請使用不同的后綴民名' else begin --為每個表建立更新記錄表 declare @tablename nvarchar(128) declare @columns varchar(8000) declare cur insensitive cursor for select name from sysobjects where xtype='u' and status>0 open cur fetch next from cur into @tablename while(@@fetch_status=0) begin set @columns='' --建立更新表 exec('select * into '[email protected][email protected]+' from '[email protected]+' where 1=0') --為更新表增加三個字段 exec('alter table '[email protected][email protected] + ' add id'[email protected]+' int identity(1,1),oprtype'[email protected]+'
char(2),oprtime'[email protected]+' datetime default getdate()') --為每個業務表建立三個觸發器 select @[email protected]+','+name from syscolumns where id=object_id(@tablename)
fetch next from cur into @tablename end close cur deallocate cur --建立總記錄更新表 exec('create table update'[email protected][email protected]+'(id numeric(18,0) identity(1,1),tablename varchar(256),oprtype
char(1),oprtime datetime default getdate())') end go
exec('if exists (select * from sysobjects where name =''update'[email protected][email protected]+''' and type=''u'')'+ 'drop table update'[email protected][email protected]) declare @tablename nvarchar(128) declare cur cursor for select name from sysobjects where xtype='u' and status>0 open cur fetch next from cur into @tablename while(@@fetch_status=0) begin --刪除更新表 exec('if exists (select * from sysobjects where name ='''[email protected][email protected]+''' and type=''u'')'+ 'drop table '[email protected][email protected]) --刪除插入觸發器 exec('if exists (select * from sysobjects where name =''tr_'[email protected]+'_i'[email protected]+''' and type=''tr'')'+ 'drop trigger tr_'[email protected]+'_i'[email protected]) --刪除刪除觸發器 exec('if exists (select * from sysobjects where name =''tr_'[email protected]+'_d'[email protected]+''' and type=''tr'')'+ 'drop trigger tr_'[email protected]+'_d'[email protected]) --刪除更新觸發器 exec('if exists (select * from sysobjects where name =''tr_'[email protected]+'_u'[email protected]+''' and type=''tr'')'+ 'drop trigger tr_'[email protected]+'_u'[email protected]) fetch next from cur into @tablename end close cur deallocate cur go
