MS SQL Trigger update call dead lock?

Question

I have two tables. Club and Coach. Between them is 0,1 - 0,1 relationship (coach can have zero or one club. club can have zero or one coach). When I want to change the coach of the given club, i have to update the club table. So i have to change idCoach of that club. Lets consider a new coach (the newly assigned coach of the given club) is already a coach of some other club. I have to set to Null field idCoach of that club, because his coach will be assigned to another club. Also if the club for which I want to change the coach, already has a coach, then I have to set idClub of that coach (in Coach table) to Null.

All the given above, also applies to when I want to change the club of the certain coach. (have to set to Null idCoach field of the previous club of a given coach and have to set to null idCoach of the new club) .

The same problems could occur when I insert new record in Club or Coach table, or if I delete the existing (in insert, update and delete I have to watch out for the corresponding references and unlink them).

I want to solve this with triggers. I guess I would have to have 3 triggers in Club table (for insert, update and delete) and 3 more in Coach table.

In update trigger (for example in Club) I have to update Coach table which will again trigger update in Club (cause in it's body it has to update Club table) and that trigger will update Coach and trigger and so forth. So I'll have a dead lock.

How to solve this?

This is the first time ever I work with triggers, so sorry in advance, for this big explanation of a simple thing.

Cheers!

Answer 1

I suggest this might not be an appropriate use for triggers, which are very difficult to debug, and often surprise people by the way they don't support single-record operations as you would expect. Break it down into simple SQL statements and wrap a transaction around it instead.

Answer 2

I recommend avoiding triggers if you can. If you must try and use Instead of triggers instead of normal triggers. The difference is instead of triggers fire before any data is actually modified. With that said I think your much better off using stored procedures, and looking to see if you

Another suggestion is if you could model this instead as an associative entity. I assume that The coach of the club must also be the club's coach. So what you could do is create a CoachAssignment table, now you only have to maintain this one record. If you ever need a coach to belong to multiple clubs the model is there for you.

With that said, you can use the If Update(FieldName) syntax to only update the tables you actually need. If you need to stop a recursive trigger you can prevent this by checking TRIGGER_NESTLEVEL()

Answer 3

Is it OK to have a 3rd table, which can hold relations? I think that will be a simple approach to work with.

Just delete the record from the new table, if the coach resigns from a club. Insert a record, for a coach joining a new club.

Hope that helps.

Answer 4

I agree with shahkalpesh - your design should include a Coach table, a Club table, a third table (ClubCoach?) to store the relationships. Then all you would have to do is enforce the rule where a coach can't be assigned to more than one club (and vice versa) externally.

Alternatively, you could just have Coach and Club tables, with idClub in the Coach table (enforcing the one Club per Coach rule), and enforce the one Coach per Club rule externally.