0

I have two tiggers on table1:

  1. trigger #1 after update it updates the last modified date column in table1
  2. trigger #2 after update if col1 is 0 it creates a log record in table2

So the problem is that when I set col1 to 0 trigger #2 creates the record properly in table2, but as trigger #1 modifies the record again, trigger #2 creates another log record in table2.

What I want is to prevent trigger #2 to be triggered when trigger #1 modifies any record.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
fishmong3r
  • 1,414
  • 4
  • 24
  • 51
  • One way would be to check in trigger #2, whether any column except `last modified` was updated – Laurence Mar 23 '15 at 14:27
  • Is there a way to check what has been modified? – fishmong3r Mar 23 '15 at 14:30
  • https://msdn.microsoft.com/en-us/library/ms189799.aspx look for "Testing for UPDATE or INSERT Actions to Specific Columns" – Laurence Mar 23 '15 at 14:31
  • check the TRIGGER_NESTLEVEL function – dean Mar 23 '15 at 14:31
  • Another option might to investigate the usage of triggers in the first place. Many (if not most) triggers can be avoided which would eliminate this issue. Aside from auditing their usage really should be minimal at best. – Sean Lange Mar 23 '15 at 14:37
  • @SeanLange, I strongly disagree with you. Triggers are important to data integrity as well as auditing and any dev who can't deal with them properly is simply incompetent. You should not be programming around the desires of the incompetent. If you devs aren't competent to deal with triggers, they aren't competent to query the database, period. So fix that rather than use the application for things that should be handled by the database. – HLGEM Mar 23 '15 at 15:08
  • @HLGEM I disagree that triggers should be used for data integrity under most situations. They can easily be disabled and you can create data that violates the integrity. Most of the time it can be handled with proper constraints. I did not say anything about devs not being able to handle them correctly. – Sean Lange Mar 23 '15 at 16:03

1 Answers1

-1

You may in the first trigger issue a disable trigger before the update (under certain circumstances, update the value, then renable the trigger.

In the first trigger:

 if (cond1) 
           disable trigger starttrigger2 on table1

    update table1

    enable trigger starttrigger2 on table1
benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22
  • Sry, but had to downvote, the suggestion is simply too dangerous to leave it be.. – dean Mar 24 '15 at 11:51
  • Is showing a developer how to disable a trigger so dangerous? – benjamin moskovits Mar 24 '15 at 13:14
  • IMHO yes, it is. In a multi-user, highly transactional environment you can't be sure what the others are doing. Have a first-hand experience in solving problems caused by it. – dean Mar 24 '15 at 13:17
  • 1
    Maybe I should have shown wrapping a transaction around the disable/enable. Unless the user uses service broker to do saving state without stepping on other tasks, I don't see a way of doing this. – benjamin moskovits Mar 24 '15 at 13:50
  • 1
    I don't see how would the transaction change anything, unless you're in serializable mode, or use the TABLOCKX hint. The point is that other users will do other DML on the table at the same time which _should_ be logged, and it won't happen if the trigger is disabled. – dean Mar 24 '15 at 13:54
  • I meant that the trigger would be guaranteed to be re-enenabled. There would have to be a lot of logic in the second trigger (or maybe making it into one trigger) to avoid what the user is trying to avoid. – benjamin moskovits Mar 24 '15 at 14:09