0

I have a table with a trigger assigned to it. And this trigger changes the same table data. Sure, this initiates a new trigger.

Every trigger instance knows (there are some rules), should it be the last one in the chain or not. And if it should, it has to turn the next trigger off.

I see the following problem: if I have a state (say, stop flag), it could work in an unexpected way. For instance, a user changes the table. A new trigger chain is being initiated. The trigger wants to be a terminator and set the stop flag up. In this moment another user changes the table => a new trigger chain is being initiated, that should be executed. But, as the stop flag is set up, it clear the flag and quits. Now, the recursive trigger (which is ignored we think) is started, looking whether the flag is cleared... Oops, it is executed!

I don't know, what is the order in such cases, will the recursive trigger be executed immediately after changing the data or the parent one is completed first, so I have no ideas, how to organize this process.

Regards,

noober
  • 4,819
  • 12
  • 49
  • 85
  • 2
    This is an extraordinarily bad way to use SQL Server. I strongly urge you to restate/restart this question from your actual needs and requirements. – RBarryYoung Jan 16 '11 at 03:30
  • 2
    @RBarryYoung: Completely agree. This is not a good use of triggers. – Mitch Wheat Jan 16 '11 at 03:35
  • You are too kind Mitch. Not to me, mind you, ... – RBarryYoung Jan 16 '11 at 03:42
  • This *ARE* my actual requirements, since end-users want to manage triggers and trigger chains that are presented in GUI as "data modification rules" and "data modification rule chains" respectively. I never replace a target with a tool, so I really need what I need. – noober Jan 16 '11 at 09:53

1 Answers1

1

Consider ditching the complicated triggers and simplifying everything into either stored procedures, or if possible, standard SQL set-based operations.

Stored procedures are easier to understand and maintain then many layers of triggers on a given table. Triggers do have value in some scenarios, but when you have triggers that invoke a chain of triggers, or have triggers that have dependencies on data being revised from other triggers, all on the same table, then you really begin to give yourself a maintenance nightmare. Simplify as a starting point by either improving your SQL update / insert statements, or refactor your triggers into a stored procedure of some sort.

Shan Plourde
  • 8,528
  • 2
  • 29
  • 42
  • There are things that can be simplified and there are things that cannot. This recursion is implicitly managed by end-users, who WANT to manage it, therefore it cannot be simplified, or another program will be the result. – noober Jan 16 '11 at 09:47
  • Yes. This is kinda database-with-a-friendly-face product that uses SQL Server for storing its data. – noober Jan 16 '11 at 14:58
  • 1
    Perhaps you could use a SQL session level solution, such as CONTEXT_INFO(). Triggers that are executed within a single DML session have access to the same CONTEXT_INFO(), see http://stackoverflow.com/questions/3025662/what-is-the-scope-of-context-info-in-sql-server, http://www.sqlservercentral.com/Forums/Topic331105-336-1.aspx, http://jasondentler.com/blog/2010/01/exploiting-context_info-for-fun-and-audit/. I'd still personally look at redesigning the whole thing, but if you're looking for the path of least resistance to get your stuff working, this may be it. – Shan Plourde Jan 16 '11 at 15:55
  • Hello, it would be great to get some feedback along with the downvote so that I can improve my answers. – Shan Plourde Oct 12 '15 at 17:47