For Any Use & On Any Platform
Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.
Course Of The Month
7 days, 19 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Trigger insert sequence from defined set of values
Posted on 2011-03-08
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3 Comments
Active today
Round robin? So every 10th record is assigned to the same person?
Try the MOD operator against the sequence.
Not 100% accurate since sequences are not gapless but something like setting assigned to: Mod(sequence.nextval,10)
Try the MOD operator against the sequence.
Not 100% accurate since sequences are not gapless but something like setting assigned to: Mod(sequence.nextval,10)
Active today
Hi,
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.
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.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Working with Network Access Control Lists in Oracle 11g (part 2)
Part 1: http://www.e-e.com/A_8429.html
Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access. For many…
How to Unravel a Tricky Query
Introduction
If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them. …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month7 days, 19 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.