Is it a good idea to put a print a trigger's name when it executes?

Question

We put a print statement at the top, so when we're running a query in SSMS the messages tab shows me that more may be happening than initially meets the eye.

Would there be much of a performance hit?

Answer 1

The performance penalty is probably negligible.

For a conclusive answer, you need to test this - benchmark how long it takes with the print statements and how long without (both on first compile with fresh statistic and on subsequent attempts).

Answer 2

I agree with @Oded - performance impact of a print statement is probably negligible. Arguably you could check sys.dm_exec_sessions for the calling @@SPID and see what application they are using, and conditionally print only if it is Management Studio. But that is going to be more expensive than just printing always in the first place. Just to show what that would look like:

IF EXISTS
(
 SELECT 1 
  FROM sys.dm_exec_sessions 
  WHERE session_id = @@SPID
  AND [program_name] LIKE '%Management Studio%'
)
 PRINT 'Trigger : <trigger name>';

To avoid copy/paste errors where the wrong trigger might be indicated because you copied the code from an existing trigger and didn't notice, I'd consider changing your new trigger template to have this code after SET NOCOUNT ON:

PRINT 'Trigger : ' + OBJECT_NAME(@@PROCID);

This is a little more expensive than just hard-coding the name obviously, but could prevent some troubleshooting headaches later if you are prone to this type of copy/paste error (I know I do it from time to time).

In any case, even if you don't want to do that, you could instead add the following line:

PRINT 'Trigger : <Trigger_Name, sysname, Trigger_Name>';

You can change the template for new triggers by going to View > Template Explorer, expanding "Trigger", right-clicking "Create T-SQL Trigger (New Menu)", and selecting Edit. Add the line above and go to File > Save. Now when you want to create a new trigger, you can open this file through the same process I just mentioned, or you can expand a table in Object Explorer, right-click "Triggers" and select "New Trigger..." When you open the code you can hit CTRL + SHIFT + M and it will give you a simple little UI to allow you to replace all of the token parameters, making it easy to enter the table name, type of action, etc. Of course when you are finished and have created the trigger you should save that script in source control, but that's a different discussion.