Inhibit SQL Server trigger execution

Question

A table with various prices per item have a trigger that will update price 3 when price 0 is changed.

The ERP that uses this database has a utility that when run updates the database with information coming from other locations.

It is possible, when this utility runs, inhibit execution of said trigger?

If it so, how can I identify when utility runs?

Answer 1

When the utility runs, just have it (or whatever calls the utility) run this command:

DISABLE TRIGGER dbo.trigger_name ON dbo.table_name;

And then afterward:

ENABLE TRIGGER dbo.trigger_name ON dbo.table_name;

It would be better to have the utility do this just around the statement(s) affecting this table specifically (and wrap that in a transaction), since the trigger is disabled for all users, not just the utility.

Another way - if the utility passes a specific application name in the connection string, or is known to be the only thing connecting as a certain user or from a certain host - is to check sys.dm_exec_connections/sys.dm_exec_sessions within the trigger.

ALTER TRIGGER dbo.trigger_name
ON dbo.table_name
FOR INSERT, ...
AS
BEGIN
 SET NOCOUNT ON;
 IF NOT EXISTS 
 (
 SELECT 1 FROM sys.dm_exec_connections
 WHERE client_net_address = '10.65.10.5' AND session_id = @@SPID
 /* or 
 SELECT 1 FROM sys.dm_exec_sessions
 WHERE host_name = N'MELVIN' AND session_id = @@SPID
 */
 /* or 
 SELECT 1 FROM sys.dm_exec_sessions
 WHERE program_name = N'My Utility App Name' AND session_id = @@SPID
 */
 )
 BEGIN
 -- trigger logic here
 END
END

If the utility logs in as a specific login separate from the main app, then you could say:

IF USER_NAME() <> N'user name' /* or SUSER_SNAME() or ORIGINAL_LOGIN() */
BEGIN
 -- trigger logic here 
END

If the utility always handles multiple rows, and the main app always only handles one row, then you could do this:

IF (SELECT COUNT(*) FROM inserted) = 1
BEGIN
 -- trigger logic here
END

If the utility could call a stored procedure instead of using ad hoc SQL, your life would be a lot easier, because you could lock the table in a transaction, disable the trigger, do your thing, then enable the trigger again. (Having this control is yet another benefit of using stored procedures and not compiling your SQL code into an app.)

If you can't fix any of these things, then you may want to look into disabling the trigger and achieving its goal in some other way. Without understanding exactly what the trigger does, it's difficult to be any more precise than that.

• PJLG: The first option it's impossible to me. It's an exe running, so I can't change it. The second one is almost I need, but... I can not use App name because it's the same name of the main app and I can not use the host name or client net address because it is possible to run the "utility" from several workstation. So, another sugestions or I want the impossible?
• Aaron Bertrand: You need some way to identify that it is the utility and not some other query. Can you not change the connection string that the utility is using? You can override the application name.
• PJLG: No, unfortunately I can not change. The issue is precisely that - how to identify if the utility is running or not ...
• Aaron Bertrand: @PJLG Does the utility log in as a different user? Does the utility always affect multiple rows, while the main app always only affects one? If not, shrug, those are the methods I know. You may need to look into magic.
• PJLG: Thank you @Aaron by your help. I found the solution. I create two another triggers to disable and enable again the first one, while the "utility" runs. In fact, after a deep analisys of queries of the "utility" with SQL Profiler, I found two tables which are updated/inserted data only with the "utility" at the begining and ending of running. So, at the begining, the new trigger disable the first one and, at the ending, the other new trigger enable the first one again (using your sugestion). Thank You!