Debugging inside an SQL Server Trigger can be incredibly difficult, since the data exists only in the scope of that trigger. However, there are a couple of techniques that can help.
Raise an error
You can output debug data from the Inserted and Deleted tables by converting it to a string via XML as follows:
Declare @ErrorInfo varchar(8000)
Set @ErrorInfo = Char(13) + 'Inserted: ' + Char(13) + IsNull ((Select * From Inserted FOR XML PATH('')), '')
Set @ErrorInfo = @ErrorInfo + Char(13) + 'Deleted: ' + Char(13) + IsNull ((Select * From Deleted FOR XML PATH('')), '')
Set @ErrorInfo = Replace (@ErrorInfo, '><', '>' + Char(13) + '<')
RaisError ('Error details: %s.', 15, 1, @ErrorInfo)Whilst this technique is simple, it’s not perfect, and may be subject to string truncation if the message gets too long.
Debug using Visual Studio
You may know that you can debug stored procedured using Visual Studio but this will also work for triggers. In SQL Server 2005 you will need to set up a server connection via Visual Studio, but in SQL Server 2008 and above you can launch into debugging directly from SQL Server Management Studio. Either way, you’ll then be able to step through your code as usual and see the values of all your variables, etc. as you go.
You can take a look at an example of this on MSDN: Walkthrough: Debugging a Transact-SQL Trigger.