[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Update a table without firing the update trigger

Posted on 2007-10-17
6
Medium Priority
?
1,863 Views
Last Modified: 2010-04-22
I have a table with an update trigger. I have a stored procedure that updates this table. If this spcific stored procedure does the update I DONT want the update trigger to fire. I can't drop and recreate the trigger because other program and procedures will be updating the table, and for those I want the trigger to fire. Any suggestions?
0
Comment
Question by:Bob Hoffman
  • 3
  • 2
6 Comments
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20094773
If the trigger isn't processing a universal business rule it shouldn't be on the table.

If your SP is encapsulating the same business rule as the trigger I'd rewrite the SP to not include it.
0
 
LVL 8

Author Comment

by:Bob Hoffman
ID: 20095020
So triggers are ONLY for processing "universal business"? What about logging before and after column values for auditing purposes.

Im soliciting suggestions on how to resolve my issue, If you have one please post it, If not please dont waste my time.

Thanks
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20095108
Yes, triggers are supposed to be ONLY for universal business rules.  Why else would you place a trigger on a table?  They can certainly be used for auditing purposes.  That's a great reason to use them.

Your issue can't be resolved in the way you want.  Triggers always fire unless you disable them.

Sorry to waste your time.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 8

Author Comment

by:Bob Hoffman
ID: 20095673
Where is it written that triggers are ONLY supposed to be used for applying universal business rules?

I don't mean to be rude to you, but all too often people seem compelled to scold others for not doing things the way THEY think it should be done. I think the purpose of this community is to help people solve their problems not chastise them for taking a different approach.

This solutions seems to work:
In the sp that does the update (prior to the update);
Write the SQL process Id to a table called SuspendTrigger, delete the row after all the updates.  

In the Update Trigger;
Check to see if its process Id is the same as what's in the SuspendTrigger table, if it is exit the trigger

Please close this question
0
 
LVL 17

Accepted Solution

by:
Chris Mangus earned 375 total points
ID: 20096267
As with any database design, nothing is written in hard and fast rules.

There is however the concept of good design as born out of years of experience in the community.

I didn't scold you nor did I chastise you.  I pointed out a potential design flaw.  I'm not the first to say that triggers should be used to process universal business rules.  Any DBA worth their salt will tell you this.

The technique you found may indeed work...assuming nothing else the same SPID as your process before the DELETE from SuspendTrigger gets processed.  You may want to run the entire batch within an explicitly defined transaction.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 375 total points
ID: 20096556
you will need, inside the trigger, need to determinate, if the update comes from the procedure or not.
one way would be to have a column in the table that identifies from where the update comes from...

alter table yourtable add UpdateSource char(1) null

create trigger trg_update
on yourtable
for update
as
begin
  if exists ( select null from inserted i where i.UpdateSource = 'P' )
  begin
    -- just reset the field to null, but don't do anything else
    update yourtable
     set UpdateSource = null
    where updatesource = 'P'        
  end
  else
  begin
    -- do the normal coding ...
   
  end
end
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

830 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