Many-To-Many Self-Join

Many-To-Many Self-Join

• Salanitro

  is it possible for you to change the database design I would suggest the following structure

  TaskID PreTaskID MasterTaskID

  1 1 1

  2 1 1

  3 2 1

  In this case you can identify all records where MasterTaskID is 1.

  Does this help

  
  

• Ajit_2005

  Alternatively, you could store pretasks in a different table and just join with it
  

• AshishM

  When a task is deleted, I need all tasks on the task chain leading to it to be deleted, unless a particular task is also part of the task chain for a different task, in which case it stays.

  The principle is: If we are not going to do A after all, there's no point in keeping the tasks that must be completed only so that we can do A.

  When I initially submitted my question, I was hoping for some comment on the two triggers I included in their entirety. When I read them, they appear to be correct, but for reasons I have not yet been able to figure out, they cause the errors I described.

  
  
  

• Ed Andersen

  Some sample data in table form would help the understanding.
  

• _Carlos_

  If I understand this right, it is a hard problem, but let me try to understand.
  
  Suppose this is your task chain:
  
  CurrentTask PreTask
  ----------- --------
  1 2
  2 3
  3 4
  4 5
  5 6
  
  If someone tries to delete task #3, you should delete all tasks, right
  
  Now suppose this is your task chain (same as above with two additional entries)
  
  CurrentTask PreTask
  ----------- --------
  1 2
  2 3
  3 4
  4 5
  5 6
  7 6
  1 7
  
  Again, suppose someone tries to delete task #3. What should you delete You might start by deleting its only pretask (#4), and then delete #5 (because it is the only pretask for #4, which you just deleted). Then you go to delete #6, because it is the only pretask for #5. But you can't delete task #6, because it is a pretask for something other than #5 (#7).
  
  How far do you roll this back Can you delete #3 at all If the last row (1, 7) were missing, would you still be unable to delete #7
  
  Now suppose this is your task chain:
  
  CurrentTask PreTask
  ----------- --------
  1 2
  2 3
  3 4
  4 5
  5 6
  7 6
  4 8
  8 7
  
  Now is it ok to delete 3, 4, 5, 6, 7, and 8
  
  My best guess at a rule is this. If task X is to be deleted, identify all tasks that depend on X through a direct chain of tasks, and call this set D(X) (include X in this set). Now consider X together with all all its pretasks, prepretasks, etc., and call this set P(X). In other words, D(X) is the directly dependent set of tasks, and P(X) is the prerequisite set of tasks.
  
  Now check to see if P(D(X)) = P(X) union D(X). If so, mark the dependents of X for deletion. Next, check to see if D(P(X)) = P(X) union D(X). If so, mark the prerequisites of X for deletion. Then delete any marked tasks.
  
  If this is right, I don't see how to handle this without some effort. I have some ideas, but none of them is easy. Hopefully you have some business rules that restrict the generality of the problem and make it easier to handle.
  
  Steve Kass
  Drew University
  
  

• v-chakri

  Unfortunately, I am using SQL Server 2000, not 2005.

  I was hoping somebody would comment on the two triggers I included in their entirety in my first posting. They appear to address the problem exactly, but they cause the errors I described in that posting.

  
  
  

• JaySch_MS

  Here is some sample data:

  tblTask
  TaskID TaskName
  ----------- ----------------------------------------------------------------------------------------------------
  1 Build house
  2 Buy lot
  3 Get lumber
  4 Hire contractor
  5 Pay gov't fees
  6 Contact broker
  7 Check World Wide Web for brokers and contractors
  8 Check newspapers for Broker ads
  9 Check phone book for brokers and contractors
  10 Research gov't fees
  11 Ask broker for info re gov't fees
  12 Search web for info re gov't fees
  15 Have contractor obtain lumber
  17 Ask friends for personal referrals
  18 Get bids from interested contractors
  19 Check newspapers for lots for sale
  86 Write program to evaluate bids
  93 Research bid evalaution techniques

  tblTaskChain
  CurrentTask PreTask
  ----------- -----------
  1 2
  1 3
  1 4
  1 5
  1 86
  2 6
  2 8
  2 19
  3 15
  4 9
  4 17
  4 18
  5 10
  5 11
  5 12
  6 7
  6 8
  6 9
  10 11
  10 12
  11 6
  15 4
  18 7
  18 9
  18 17
  86 93
  

  
  
  

• Keir Gordon

  Sheldon,

  The error message "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)." means that you've blown the recursion stack. You can't have a procedure call itself more than 32 times before SQL Server stops the procedure.

  I think that the problem here is that your delete triggers always runs a delete against the other table, which then always runs a delete against the table that initiated the trigger. Here's a sort of "pseudocode" example that I think might make clear what's going on.

  /*This is the outside statement that starts the ball rolling.*/
  delete tblTaskChain where <some criteria>

  /* The delete triggers the following. */
  utrTaskChain_del:
  <fill OtherCurrent table>
  delete tblTask <some criteria>

  /* That delete sets off the trigger on the other table. */
  utrTask_del:
  delete tblTaskChain where <some criteria>

  /* Which sets off the trigger on the first table. */
  utrTaskChain_del:
  <fill OtherCurrent table>
  delete tblTask <some criteria>

  /* Even though the delete didn't actually remove anything from the table this time, the delete was still called, so the trigger gets set off. */
  utrTask_del:
  delete tblTaskChain where <some criteria>

  /* And as you can see, it doesn't stop. */

  I don't see where the recursion will ever stop. So what happens is that the code goes back and forth 32 times and then on the 33rd SQL Server says that this has gone over the limit and throws the error message you saw.

  A solution is to check to see whether or not you need to delete before you actually call it. That way when you get to the end of a branch to delete you can be sure that it will terminate.

  If you want more information about recursion in SQL Server, I found this site to be useful: http://msdn.microsoft.com/library/default.asp url=/library/en-us/dnsqlpro03/html/sp03i8.asp

  Nick Stipanovich
  SQL Server Build Team

  
  

• AlEvan

  Thank you for your suggestion, Brokenrulz, but no, it doesn't help.

  If I understand you correctly, MasterTaskID would represent the "root" task of which all the others are branches. This might be useful if I wanted to delete an entire tree of tasks, but that is rarely the case.

  In my database, each "root task" (i.e., each task that is not a pretask for any other task) may have any number of pretasks and each of those pretasks may also have any number of pretasks, etc. If any task in the tree is deleted, I need the pretask chain leading up to that task only to be deleted as well. Tasks that lead to the root by some other chain must not be deleted, nor tasks that are also part of a chain leading to some other root. I need to be able to cut any branch off the tree, leaving all other branches and the root intact.

  
  
  

• TSVCorp

  Thank you, Nick.

  Do understand correctly that the Delete event gets fired even if no records were actually deleted

  Suppose I preceded the delete statements in the two triggers with:

  declare @cnt int
  set @cnt = count(*) from deleted
  if @cnt > 0 BEGIN
  ...

  Would that do the job (I'll know before you reply to this because I'm gonna try it right now!)

  Thanks again.

  Sheldon

  
  
  

• Gavin Clark

  if the database is sql 2005, you can use recursive queries ot get your require data The sample below gives all Pretask that are for CurrentTask ID 10.

  WITH CTE(CurrentTask,PreTask)

  AS

  (

  SELECT T1.CurrentTask,T1.PreTask

  FROM tblTaskChain T1

  WHERE T1.CurrentTask=10

  UNION ALL

  SELECT T2.CurrentTask,T2.PreTask

  FROM tblTaskChain T2

  INNER JOIN CTE

  ON

  T2.CurrentTask=CTE.PreTask

  )

  SELECT CurrentTask,PreTask FROM CTE

  
  
• Many-To-Many Self-Join