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'.