SQL:游标生成触发脚本

为了跟踪当前操作界面会写入哪个表，对系统表做一个写表的日志，

快速生成所有表的触发器脚本

declare c_tbl cursor for 
 SELECT OBJ.NAME AS [TABLE_NAME],obj.object_id as table_object_id    
FROM SYS.ALL_OBJECTS OBJ
where 1=1 
 and obj.type='U'
 AND OBJ.object_id>0 ;
 declare @fetch_status_tbl int,
    @table_name nvarchar(128),
	@table_object_id bigint;
begin
/**
-- 写表的日志表
  create table Write_Table_Log(
   log_id int identity(1,1) primary key,
   table_name nvarchar(128),
   primary_field nvarchar(128),
   [event] nvarchar(128),
   creation_date datetime,
   [value] nvarchar(128)
   ); 
*/
   open c_tbl ;
   fetch next from c_tbl into @table_name, @table_object_id 
   set @fetch_status_tbl = @@FETCH_STATUS
   while @fetch_status_tbl = 0
   begin
     
	 print 'if object_id(''' +@table_name +'_rv_tr'',''TR'') IS NOT NULL ';
	 print '  drop trigger [' +@table_name +'_rv_tr];';
	 print 'GO ';
	 print 'Create Trigger [' +@table_name +'_rv_tr] on [' +@table_name +'] ';
	 print ' for insert, update '
	 print 'as '
	 print 'begin '
	 print '  declare @AutoID nvarchar(120) ';
	 print '  select @AutoID = null from inserted';
	 print ' insert into dbo.Write_Table_Log([table_name],primary_field,[event],creation_date,[value]) ';
	 print ' values( '''+@table_name+''',null,''update'',getdate(),@AutoID);';
	 print 'end;';
	 print 'GO'
	 print ' ';
     fetch next from c_tbl into @table_name, @table_object_id 
     set @fetch_status_tbl = @@FETCH_STATUS
   end;
   close c_tbl;
   deallocate c_tbl;
end;

　　待解决问题：

1、确定表主键

2、主键对应的值。

补充解决问题的脚本：

declare c_tbl cursor for 
 SELECT OBJ.NAME AS [TABLE_NAME],obj.object_id as table_object_id    
FROM SYS.ALL_OBJECTS OBJ
where 1=1 
 and obj.type='U'
 AND OBJ.object_id>0 ;
 declare @fetch_status_tbl int,
    @table_name nvarchar(128),
	@table_object_id bigint,
    @pk_field nvarchar(128);

begin
/**
-- 写表的日志表
  create table Write_Table_Log(
   log_id int identity(1,1) primary key,
   table_name nvarchar(128),
   primary_field nvarchar(128),
   [event] nvarchar(128),
   creation_date datetime,
   [value] nvarchar(128)
   ); 
*/
   open c_tbl ;
   fetch next from c_tbl into @table_name, @table_object_id 
   set @fetch_status_tbl = @@FETCH_STATUS
   while @fetch_status_tbl = 0
   begin
      set @pk_field ='';
	 -- 1、获取主键字段名称 
      select top 1 @pk_field = pkcol.name 
		 from 
		 [sys].[sysindexkeys]  PK,   -- 主键
		 sys.all_columns PKCOL 
		 where PKCOL.column_id = PK.colid AND PKCOL.OBJECT_ID = PK.ID 
		  and PK.INDID= 1  
		 and pk.id = @table_object_id;
      -- 2、当没有主键时，获取表中第一个自动增加序号的字段
	  if isnull(@pk_field,'') ='' 
	     select top 1 @pk_field = pkcol.name 
		   from sys.all_columns PKCOL 
		   where pkcol.object_id = @table_object_id
		   and pkcol.is_identity=1;
      -- 3、当没有主键时，获取表中第一个非空的字段
	  if isnull(@pk_field,'') ='' 
	     select top 1 @pk_field = pkcol.name 
		   from sys.all_columns PKCOL 
		   where pkcol.object_id = @table_object_id
		   and pkcol.is_nullable=0;   
      -- 4、当没有主键时，获取表中第一个字段
	  if isnull(@pk_field,'') ='' 
	     select top 1 @pk_field = pkcol.name 
		   from sys.all_columns PKCOL 
		   where pkcol.object_id = @table_object_id
		   ; 
		   
	 print 'if object_id(''' +@table_name +'_rv_tr'',''TR'') IS NOT NULL ';
	 print '  drop trigger [' +@table_name +'_rv_tr];';
	 print 'GO ';
	 print 'Create Trigger [' +@table_name +'_rv_tr] on [' +@table_name +'] ';
	 print ' for insert, update '
	 print 'as '
	 print 'begin '
	 print '  declare @AutoID nvarchar(120) ';
	 print '  select @AutoID = '+@pk_field+' from inserted';
	 print ' insert into dbo.Write_Table_Log([table_name],primary_field,[event],creation_date,[value]) ';
	 print ' values( '''+@table_name+''','''+@pk_field+''',''UPDATE'',getdate(),@AutoID);';
	 print 'end;';
	 print 'GO'
	 print ' ';
     fetch next from c_tbl into @table_name, @table_object_id 
     set @fetch_status_tbl = @@FETCH_STATUS
   end;
   close c_tbl;
   deallocate c_tbl;
end;