Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

trigger not to execute

Posted on 2004-09-08
8
Medium Priority
?
489 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

618 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