SQL Server 2012 – How to Inhibit Trigger Execution

sql-server-2012trigger

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?

Best Answer

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.

Related Solutions

MySQL – How to Disable Trigger for One Table

Actually, if you place an if then block in every trigger, you could effectively shutdown all triggers. Here is such a code block

IF @TRIGGER_DISABLED = 0 THEN
    ...trigger body
END IF;

In the mysql environment, you could

run SET @TRIGGER_DISABLED = 1;
do your data maintenance
run SET @TRIGGER_DISABLED = 0;

So your trigger for table A should look like this:

BEGIN 
    IF @TRIGGER_DISABLED = 0 THEN
    IF (OLD.status != 1 AND NEW.status = 2) THEN 
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL) THEN 
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        END IF; 
    ELSEIF (OLD.Status = 1 AND NEW.Status != 2) THEN 
        IF (NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN 
            INSERT INTO geo (datatype, foreignid, long, lat, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status); 
        END IF; 
    ELSEIF (NEW.status != 3) THEN   
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL AND (NEW.geo_lat IS NULL OR NEW.geo_long IS NULL)) THEN 
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        ElSEIF ((OLD.geo_lat IS NULL OR OLD.geo_long IS NULL) AND NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN 
            INSERT INTO geo (datatype, foreignid, longitude, latitude, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status); 
        ELSEIF (OLD.geo_lat!=NEW.geo_lat OR OLD.geo_long != NEW.geo_long OR OLD.status != NEW.status) THEN 
            UPDATE geo SET lat = NEW.geo_lat, long = NEW.geo_long, status = NEW.status WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        END IF; 
    END IF; 
    END IF; 
END

So your trigger for table B should look like this:

CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`     
    FOR EACH ROW     
    BEGIN     
        IF @TRIGGER_DISABLED = 0 THEN
            CALL sp-set-comment_count(NEW.`gid`);     
        END IF;
    END;

If you want the triggers for table A to launch but not to table B, then add the code block only to table B's trigger.

MySQL After Update Trigger apparently not running

My initial guess would be based on the trigger's flow. Why ?

According to the Book

MySQL Stored Procedure Programming

Chapter 11 Page 251 Paragraph 3 says the following:

The most significant difference between BEFORE and AFTER triggers is that in an AFTER you are not able to modify the values about to be inserted into or updated with the table in question -- the DML has executed, and it is too late to try to change what the DML is going to do.

From the this statement, this is what I understand: Since the DML is executed and the DML is still spoken of in the future imperfect tense (I am not an English teacher), the new values are really not available to be read in a SELECT since the trigger is not done.

In your case, you are doing an AFTER UPDATE on phpbb_rathermenacing.phpbb_bbdkp_memberlist. With the trigger being 99.99999999% done, you are attempting to do the following in the third clause of the IF statement:

        ((select min(m.member_rank_id) 
            from phpbb_bbdkp_memberlist m 
            WHERE m.phpbb_user_id = NEW.phpbb_user_id AND 
                  m.member_guild_id = NEW.member_guild_id AND 
                  m.member_id != NEW.member_id) >= new.member_rank_id OR 
        (select count(m.member_rank_id) 
           from phpbb_bbdkp_memberlist m 
          WHERE m.phpbb_user_id = NEW.phpbb_user_id AND 
                m.member_guild_id = NEW.member_guild_id AND 
                m.member_id != NEW.member_id) = 0)

You are asking for the count and state of the table phpbb_bbdkp_memberlist that has not yet been changed. Regardless of the Storage Engine, I am very sure you will never get a response because the trigger has to finish in order for the Storage Engine to even think about posting changes to disk.

I have two old posts where I discuss why a trigger is no place for business intelligence:

Aug 11, 2011 : Policies RE database triggers in well-designed applications?
Aug 15, 2011 : Performance of a Trigger vs Stored Procedure in MySQL

SUGGESTIONS

Make that trigger a stored procedure
Remove the AFTER UPDATE trigger
Run the stored procedure after the UPDATE in your application.

Best Answer

Related Solutions

MySQL – How to Disable Trigger for One Table

MySQL After Update Trigger apparently not running

SUGGESTIONS

Related Question