Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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? –  PJLG Jun 19 at 19:00
    
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. –  Aaron Bertrand Jun 19 at 19:04
    
No, unfortunately I can not change. The issue is precisely that - how to identify if the utility is running or not ... –  PJLG Jun 20 at 6:37
1  
@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. –  Aaron Bertrand Jun 20 at 10:52
    
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! –  PJLG Jun 20 at 14:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.