Solved

trigger not to execute

Posted on 2004-09-08
8
487 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 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

728 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