Solved

trigger not to execute

Posted on 2004-09-08
8
475 Views
Last Modified: 2012-05-05
is there a way for a stored procedure to tell a trigger not to execute.
0
Comment
Question by:vinny45
8 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 12011432
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)
   Begin
   -- Your code goes here


   End
0
 
LVL 12

Expert Comment

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


0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12011524
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.
0
 

Expert Comment

by:Ernesto_Che_Guevara
ID: 12011592
>>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.  
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 12

Expert Comment

by:patrikt
ID: 12011624
Why do you thing. Just table owner must be enough.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12011879
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
Else
  Begin

    ... real trigger action ...

  End

Note: I haven't tested this; you might encounter some odd transactional behavior, so test first!
0
 
LVL 34

Expert Comment

by:arbert
ID: 12013252
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 :)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12013549
>>I've also used AC's method of adding a dummy column....<<
And I wonder who he got it from :)
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now