Trigger insert sequence from defined set of values
I am not quite sure how to describe it but I have an application where end users submit accounts to be worked and they all go in a table. I am in need of assigning these accounts to a group of ten other users for example. These ten users have their own personal user id's.
Thus when the account is inserted into the table a trigger needs to insert a specific user id in the "Assigned" column and do so in a sequence to ensure an equal number of accounts per person. Just not sure how to design this.
Thanks in advance.
Thus when the account is inserted into the table a trigger needs to insert a specific user id in the "Assigned" column and do so in a sequence to ensure an equal number of accounts per person. Just not sure how to design this.
Thanks in advance.
Who is Participating?
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
How do you need to manage concurrency and rollbacks ?
- what if the transaction that inserts do not commit ? The user assignation is rolled back and the user should available for another assignation.
- what if several concurrent inserts ? Each insert will not see the non-committed assignation, and finally when they all commit they will assign too much to a user.
Sequences (or random number) will be the most efficient, by result may not be balanced over users (gaps, rollbacks)
Keeping the number of assignation for each user in a table will solve that, but you will have concurrency issues (locks) when inserting.
Maybe you can change the design: instead of assigning at insert time, you should assign it when you query what the user has to work on: just pick the next 10 accounts from the queue (queue being the non-assigned accounts).
Regards,
Franck.