Clever Geek Handbook

MS SQL Trigger update causing deadlock?

I have two tables. Club and coach. The ratio between them is 0.1 - 0.1 (a coach can have zero or one club, a club can have zero or one coach). When I want to change the coach of a given club, I need to update the club table. So I need to change the idCoach of this club. Let's consider that the new coach (the newly appointed coach of that club) is already the coach of another club. I have to set the Null field idCoach for this club because his coach will be assigned to another club. Also, if I already have a coach in the club where I want to change the coach, then I need to set the idClub of this coach (in the coach's table) to Null.

Everything above also applies to when I want to change the club of a particular coach. (you need to set in the Null idCoach field of the previous club of this coach and set the new club's idCoach to zero).

The same problems can occur when I insert a new record into the Club or Coach table, or if I delete existing ones (insert, update and delete, I have to watch for the corresponding links and disable them).

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

In an update trigger (for example in a Club), I need to update the Coach table, which will trigger an update in the Club again (resulting in it needing to update the Club table), and this trigger will update the Coach and trigger, and so on. Therefore, I will have a dead castle.

How to solve this?

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

Hooray!

+1


source to share


4 answers


I suggest that this may not be appropriate for triggers that are very difficult to debug, and often surprise people that they don't support single write operations as you'd expect. Divide it into simple SQL statements and close the transaction around it.



+2


source


Is it good to have a third table that can contain relationships? I think this will be a simple approach to work.

Just remove the entry from the new table if the coach leaves the club. Insert the entry to have the coach join the new club.



Hope it helps.

+2


source


I recommend avoiding triggers if possible. If you should try and use instead of triggers instead of normal triggers. The difference is not the fire being fired, but the fact that the data has actually been changed. With that said, I think you are much better off using stored procedures and see if you can

Another suggestion is that you could model this as an associative entity. I assume that the club coach should also be the club coach. So what you can do is create a CoachAssignment table, now you only need to store one record. If you ever need a trainer who will belong to multiple clubs, the model is for you.

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

0


source


I agree with shahkalpesh - your design should include a coaches table, a club table, a third table (ClubCoach?) To store relationships. Then all you have to do is introduce a rule that a coach cannot be assigned to more than one club (and vice versa) from the outside.

Alternatively, you could simply use the coach and club tables, with idClub in the Coach table (enforce the one club per coach rule) and enforce the Coach per Club rule externally.

0


source






More articles:


All Articles