Cursor or Trigger ?

  • I've never used a Cursor or a Trigger before. I have a customer table that I want to assign agent_ids to. Upon insert of each new customer in the customer table an agent from the agent list (agent table) should be assigned to a customer . We want the agents to be added in sequential order giving each agent an equal amount of customers. The agent list can change at any moment and may have 3 agents one week and 20 the next.

    Having no experience with triggers or cursors which should I use? 

    The Trigger/Cursor would only run upon insert of a new customer so I'd have to determine the most recent agent_id that was applied then start after that paticular agent_id. The agent_id is not in numerical order so I'd have to find that paticular agent_id's Identity Seed ID and increment it by one.

    ----CustomerTable---              ---AgentTable-----

    ID   Customer  Agent_ID               ID   Agent_Id

    1       Test        1952                    1      165

    2       Test2       165                     2      1952

     

    Any suggetions? TIA 

  • Definately not cursor.

    You can do this either in your stored procedure that perform the insert into the customer table. Or you can use an INSERT trigger to do this.

    If you have problem with the query to perfrom the agent assignment, please do post more information on the this.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply