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
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
Be aware that you have to force to
TextMode = false otherwise you will get an
ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. SqlException: Invalid object name 'TRIGGERNAME'.