是否可以创建一个触发器，根据某些条件更新某些行？
表：
- 员工
- 订单
- 客户
一名员工可以接受 0 个或多个客户的订单，而一个客户可以下 1 个或多个订单。从这个意义上说，Orders 是 Employee 和 Customer 之间的桥梁，并且包含 Employee_no 和 Customer_no 作为 FK。
我想创建一个触发器，在将新客户记录添加到客户表后，按以下方式更新订单表：
- 例如，前五个客户分配给 Employee_no 1。
- 例如，后五个客户被分配给 Employee_no 2。
...依此类推，直到最后一个 Employee_no（例如 10）
，然后另外 5 个客户再次分配给 Employee_no 1。
就这样，周而复始，员工轮流接待顾客。
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 1, 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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
听起来您希望在订单表而不是客户表上使用触发器。也许这样的东西会起作用？
编写一个查询，获取员工在 Order 表中拥有的 5 个订单的完整倍数。
使用此查询编写一个函数，返回下一个分配订单的员工的员工 ID。测试该函数以确保它似乎执行您想要的操作。
使用函数在订单表中设置employee_id 编写插入触发器。
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.