開發庫測試庫間觸發器同步 查找某時間點後的觸發器 生成其刪除 創建 禁用的腳本

示例效果：

為便於資料庫間的腳本移植，

（如在開發庫和測試庫間進行腳本同步）

獲取某時間點後，資料庫增修的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.相應腳本進行驗證