Alter SQL trigger created by TableDependency package

We are using a wonderful package called TableDependency which greatly helps the creation & use of SQL trigger events in C#.

This package is fulfilling 99% of our scenarios but one is to be able to replay an event generated by the trigger without altering the data.
But TableDependency trigger include some protection preventing to be triggered if no column value are actually changed. This is implemented with an EXCEPT clause

        BEGIN
            SET @dmlType = 'Update'
INSERT INTO @modifiedRecordsTable SELECT [field1] FROM  
(SELECT [m_New].[field1] FROM INSERTED AS [m_New] EXCEPT SELECT [m_Old].[field1] FROM DELETED AS [m_Old]) a
        END

The easiest way for temporarily replay event is to remove this clause and alter the trigger

        BEGIN
            SET @dmlType = 'Update'
INSERT INTO @modifiedRecordsTable SELECT [field1] FROM  
(SELECT [m_New].[field1] FROM INSERTED AS [m_New] ) a
        END

this can be done in SSMS

but doing this on regular basis, even if not a long action is cumbersome plus risk of typo error. Let's quickly automate this change with Microsoft.SqlServer.Smo assemblies (Server Management Object) in order to work with typed objects rather pure TSQL.

A simple regex take care of removing all the EXCEPT clause.
Be aware that you have to force to TextMode = false otherwise you will get an Microsoft.SqlServer.Management.Smo.FailedOperationException exception

ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch.  
SqlException: Invalid object name 'TRIGGERNAME'.  

Fabien Camous

Read more posts by this author.