【土法煉鋼】為所有資料表增加Trigger ，記錄(Insert, Update, Delete)

在清理evernote時看到以前的DIY還是忍不住笑了出來。
這stored procedure 出世的原因是當初接手一個開發中及由兩代人編寫的項目，就想出這土法來追查bug。
Tigger保存的格式是XML，也不是很好用，一直都是在別無選擇下使用。
到了現在都改用CDC了。

CREATE TABLE dbo.WormsRows

     (

     dt datetime NULL,

     host varchar(50) NULL,

     SPID int NULL,

     tbl nvarchar(50) NULL,

     mode nchar(10) NULL,

     xml xml NULL

     )  ON [PRIMARY]

      TEXTIMAGE_ON [PRIMARY]

GO

DECLARE @sql varchar (8000), @TABLE_NAME sysname

SELECT @TABLE_NAME = MIN(name) FROM sys .tables

WHILE @TABLE_NAME IS NOT NULL

     BEGIN

      SELECT @sql = 'CREATE TRIGGER [TR_' + @TABLE_NAME + '_CUD] ON [' + @TABLE_NAME +'] '

       + 'FOR INSERT, UPDATE, DELETE AS '

       + 'IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted) '

       + 'INSERT INTO WormsRows (dt, host,SPID,  tbl, mode, xml) VALUES     (GETDATE(), HOST_NAME(), ''' + @TABLE_NAME + ''' ,''In'', (select * from inserted for XML auto, root) ) '

       + 'IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) '

       + 'INSERT INTO WormsRows (dt, host, tbl, mode, xml) VALUES     (GETDATE(), HOST_NAME(), ''' + @TABLE_NAME + ''' ,''Up'', (select * from (select ''inserted'' as act, * from inserted union select ''deleted'' as act, * from deleted )a for xml auto, root) ) '

       + 'IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) '

       + 'INSERT INTO WormsRows (dt, host, tbl, mode, xml) VALUES     (GETDATE(), HOST_NAME(), ''' + @TABLE_NAME + ''' ,''Del'', (select * from deleted for XML auto, root) ) '

     SELECT @sql

     begin try

         EXEC(@sql )      

     end try

     begin catch

         print '產生Trigger 失敗:' +  @TABLE_NAME

     end catch

     --下一個資料表

     SELECT @TABLE_NAME = MIN (name) FROM sys.tables where name > @TABLE_NAME

END