开发库测试库间触发器同步 查找某时间点后的触发器 生成其删除 创建 禁用的脚本

示例效果：

为便于数据库间的脚本移植，

（如在开发库和测试库间进行脚本同步）

获取某时间点后，数据库增修的Triggers，

生成这些Triggers的创建脚本；

 

相关步骤：

1.通过SMSS 连接数据库，打开一个SQL窗口；

 

2.右键该窗口  Results to -》Results to text

  右键该窗口 Query Options -》Results - Text -》 取消勾选 Include column headers in the result set

 

3. 执行如下sql，得到其文本结果信息

 

declare @trigname nvarchar(100)=N'';
--查找某时间点后的增修的Trigger
declare curProc cursor for
select 
 [name]
FROM 
 sys.all_objects 
where 
 type_desc = N'SQL_TRIGGER' 
 and is_ms_shipped = 0
 and substring([name],1,4)  in ('TRG_')
 and modify_date >='2017-08-17 00:00:00'
order by 
 modify_date desc;

open curProc;
fetch next from curProc into @trigname;
while @@FETCH_STATUS = 0 
begin
 --如存在 则删除
 print(N'IF EXISTS(SELECT 1 FROM SYS.OBJECTS WHERE [NAME]=N''' + @trigname +''' AND [TYPE]=''TR'')')
 PRINT(N'DROP TRIGGER ' + @trigname);
 print N'GO';

 --重建
 exec ('sp_helptext ' + @trigname);
 print N'GO';

 fetch next from curProc into @trigname;
end
close curProc;
deallocate curProc;



declare @trigname2 nvarchar(100)=N'';
declare @tablename2 nvarchar(100)=N'';
--查找当前表及触发器的禁用状态  仅过滤需要禁用的
declare curProc2 cursor for
SELECT
[Name] as TriggerName,
object_name(parent_object_id) as TableName 
FROM sys.all_objects
WHERE  [TYPE]='TR'
 and  is_ms_shipped = 0
 and modify_date >='2017-08-10 00:00:00'
 and 1=OBJECTPROPERTY(object_id, 'ExecIsTriggerDisabled')  
;

open curProc2;
fetch next from curProc2 into @trigname2, @tablename2;
while @@FETCH_STATUS = 0 
begin
  --按需 禁用 
 print N'';
 PRINT(N'ALTER TABLE ' + @tablename2 +' DISABLE  TRIGGER  ' + @trigname2) +N' ; ';
 print N'GO';
 
 fetch next from curProc2 into @trigname2, @tablename2;
end
close curProc2;
deallocate curProc2;

4.相应脚本进行验证