Solved

Trigger insert sequence from defined set of values

Posted on 2011-03-08
3
544 Views
Last Modified: 2013-12-19
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.
0
Comment
Question by:mjfigur
3 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35076952
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)
0
 
LVL 15

Accepted Solution

by:
Franck Pachot earned 500 total points
ID: 35082102
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.
0
 

Author Closing Comment

by:mjfigur
ID: 35089533
Thank you that is such a great idea. Will have it pull dynamically assigning them when they are requested will allow for my fluidity especially when one of the users may be out of the office on a given day or week.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
JDeveloper 12c for 32 bit 4 84
Converting a row into a column 2 53
Export table into csv file in oracle 10 87
Repeat query 13 46
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question