• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

trigger not to execute

is there a way for a stored procedure to tell a trigger not to execute.
1 Solution
Anthony PerkinsCommented:
The easiest kludge that I have discovered is to add a column (SkipTrigger) of data type bit in the table and set it appropriately in the Insert/Update/Delete statement.  Then in your trigger:

You can do something like:
If Not Update(SkipTrigger)
   -- Your code goes here

There is possibility to disable trigger for some time by ALTER TABLE table DISABLE TRIGGER trigger.
And then back by ENABLE.

another kludge is to create a view on the table, and put the trigger on the view instead of the table. if you want to fire the trigger insert/update/delete the view, otherwise hit the table directly.
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

>>There is possibility to disable trigger for some time by ALTER TABLE table DISABLE TRIGGER trigger<<
That is true and I should have mentioned that.  There is just one problem with that (and I suspect this is a major one for most shops), you need SysAdmin permissions.  
Why do you thing. Just table owner must be enough.
Patrikt is right:

"ALTER TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable."

However, it's fairly moot since odds are that the table owner is dbo  :-)

If you must use a trigger (I'd highly recommend seeing if you can have all data stuff go through stored procedures), here's an alternative that's less intrusive than acperkins' solution, but I wouldn't use it in a table with lots of concurrent updates:

Create Table SkipTr(SPID Integer)

In your stored procedure, do this right before the update:

Insert Into SkipTr(@@SPID)

In the trigger:

If Exists(Select * From SkipTr Where SPID = @@SPID)
  Delete From SkipTr Where SPID = @@SPID

    ... real trigger action ...


Note: I haven't tested this; you might encounter some odd transactional behavior, so test first!
Not to mention that altering a table will also disable the trigger system wide and not just for your process.....

I've also used AC's method of adding a dummy column....

SjoerdVerweij , very interesting idea--might have to toy with that :)
Anthony PerkinsCommented:
>>I've also used AC's method of adding a dummy column....<<
And I wonder who he got it from :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now