Solved

trigger not to execute

Posted on 2004-09-08
8
485 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

821 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