SQL:游标生成触发脚本

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
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、主键对应的值。

补充解决问题的脚本:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
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;

  

 

posted @   samrv  阅读(36)  评论(1编辑  收藏  举报
相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
历史上的今天:
2022-07-21 EBS: HINT +INDEX 提示使用多个索引
点击右上角即可分享
微信分享提示