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 ; |
优质生活从拆开始
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
2022-07-21 EBS: HINT +INDEX 提示使用多个索引