--
为synchroflow_log数据库里面的每个表加一个log_mode字段
use synchroflow_log
exec sp_MSforeachtable ' alter table ? add log_mode char(2) '
-- 创建mainMoniter表
create table mainMoniter(mode char ( 2 ),tablename varchar ( 50 ),changetime char ( 20 ))
-- 将synchroflow数据库里面的表的名称插入到一个临时表
SELECT name into #tt FROM sysobjects WHERE (type = ' u ' )
use synchroflow
-- 定义游标,通过游标为每个表建立触发器
declare @table_name varchar ( 100 )
declare cursor_temp cursor for select name from #tt
open cursor_temp
fetch next from cursor_temp into @table_name
while @@fetch_status = 0
begin
declare @sql_A varchar ( 1000 )
declare @sql_U varchar ( 1000 )
declare @sql_D varchar ( 1000 )
set @sql_A = ' create trigger ' + @table_name + ' _A on ' + @table_name + ' for insert as insert into synchroflow_log.dbo. ' + @table_name + ' select *, '' A '' as log_mode from inserted insert into synchroflow_log.dbo.mainMoniter select '' A '' , ''' + @table_name + ''' ,convert(char(19),getdate(),120) from inserted '
set @sql_U = ' create trigger ' + @table_name + ' _U on ' + @table_name + ' for update as insert into synchroflow_log.dbo. ' + @table_name + ' select *, '' U '' as log_mode from deleted insert into synchroflow_log.dbo.mainMoniter select '' U '' , ''' + @table_name + ''' ,convert(char(19),getdate(),120) from deleted '
set @sql_D = ' create trigger ' + @table_name + ' _D on ' + @table_name + ' for delete as insert into synchroflow_log.dbo. ' + @table_name + ' select *, '' D '' as log_mode from deleted insert into synchroflow_log.dbo.mainMoniter select '' D '' , ''' + @table_name + ''' ,convert(char(19),getdate(),120) from deleted '
exec ( @sql_A )
exec ( @sql_U )
exec ( @sql_D )
fetch next from cursor_temp into @table_name
end
close cursor_temp
deallocate cursor_temp
-- 删除临时表
drop table #tt
use synchroflow_log
exec sp_MSforeachtable ' alter table ? add log_mode char(2) '
-- 创建mainMoniter表
create table mainMoniter(mode char ( 2 ),tablename varchar ( 50 ),changetime char ( 20 ))
-- 将synchroflow数据库里面的表的名称插入到一个临时表
SELECT name into #tt FROM sysobjects WHERE (type = ' u ' )
use synchroflow
-- 定义游标,通过游标为每个表建立触发器
declare @table_name varchar ( 100 )
declare cursor_temp cursor for select name from #tt
open cursor_temp
fetch next from cursor_temp into @table_name
while @@fetch_status = 0
begin
declare @sql_A varchar ( 1000 )
declare @sql_U varchar ( 1000 )
declare @sql_D varchar ( 1000 )
set @sql_A = ' create trigger ' + @table_name + ' _A on ' + @table_name + ' for insert as insert into synchroflow_log.dbo. ' + @table_name + ' select *, '' A '' as log_mode from inserted insert into synchroflow_log.dbo.mainMoniter select '' A '' , ''' + @table_name + ''' ,convert(char(19),getdate(),120) from inserted '
set @sql_U = ' create trigger ' + @table_name + ' _U on ' + @table_name + ' for update as insert into synchroflow_log.dbo. ' + @table_name + ' select *, '' U '' as log_mode from deleted insert into synchroflow_log.dbo.mainMoniter select '' U '' , ''' + @table_name + ''' ,convert(char(19),getdate(),120) from deleted '
set @sql_D = ' create trigger ' + @table_name + ' _D on ' + @table_name + ' for delete as insert into synchroflow_log.dbo. ' + @table_name + ' select *, '' D '' as log_mode from deleted insert into synchroflow_log.dbo.mainMoniter select '' D '' , ''' + @table_name + ''' ,convert(char(19),getdate(),120) from deleted '
exec ( @sql_A )
exec ( @sql_U )
exec ( @sql_D )
fetch next from cursor_temp into @table_name
end
close cursor_temp
deallocate cursor_temp
-- 删除临时表
drop table #tt