Is it possible to create a trigger that updates certain rows based on certain conditions?

Tables:

• Employee
• Orders
• Customer

An employee can take orders from 0 or more customers, and a customer can place 1 or many orders. In this sense, Orders is the bridge between Employee and Customer, and contains Employee_no and Customer_no as FKs.

I would like to create a trigger that after a new customer record has been added to Customer table, updates the Orders table in the following way:

• First five customers are assigned to Employee_no 开发者_如何学Python1, for instance.
• Second five customers are assigned to Employee_no 2, for instance.

... and so on till the last Employee_no (say for instance 10)

And then another five customers are assigned to Employee_no 1, again.

So, it goes round and round and the employees take turns to handle customers.

It sounds like you would like a trigger on the Order table, rather than the Customer table. Perhaps something like this would work?

• Write a query to get number of complete multiples of 5 orders the employees has in the Order table.

• Use this query to write a function returning the employee ID for the employee that is next in turn to get assigned an order. Test the function to ensure it seems to do what you want.

• Write a trigger on insert using the function to set employee_id in Orders table.

I'd use a trigger on the order table that determines the employee with the least customers. That prevents you from having to re-evaluate all existing orders.

create or replace trigger trigger_orders
before insert on orders
for each row
begin
  if :new.employee_no is null
  then
    ... determine employee with least orders and assign this no to :new.employee_no ...
  end if;
end;