关于PL/SQL触发器的另一个问题
我遇到了第二个触发器的问题，我们要编写以下内容：
我刚刚编写了一个存储过程和存储函数，用于将新行插入到我的 Orders 表中。行更新插入：Ordernum、OrderDate、Customer、Rep、Manufacturer、Product、Qty 和 SaleAmount。
我现在必须编写一个触发器，通过添加新添加的销售金额来更新我的 Offices 表。问题是，并非每个销售代表都分配有一个办公室。我不明白我是否需要在“FOR EACH ROW”下有一个以某种方式规定这一点的when子句，或者我是否需要在我的SET Sales行之后进行规定。到目前为止，这是我的代码，但不幸的是，它更新了所有办公室销售，而不仅仅是销售代表所属的销售：
CREATE OR REPLACE TRIGGER UpdateOffices
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
UPDATE Offices
SET Sales = Sales + :NEW.Amount
WHERE Office IN (SELECT RepOffice
FROM Salesreps
WHERE RepOffice IS NOT NULL);
End;
/
SHOW ERRORS
Sales 是 Offices 表上的列的名称。存储过程中使用的名称的数量。
I have run into a problem with a second trigger we are to write regarding the following:
I have just written a stored procedure and stored function that serve to insert a new row into my Orders table. The row update inserts: Ordernum, OrderDate, Customer, Rep, Manufacturer, Product, Qty, and SaleAmount.
I now have to write a trigger that updates my Offices table by adding the amount of the newly added sale. Problem is, not every salesrep has an office assigned to them. I don't understand if I need to have a when clause under 'FOR EACH ROW' which somehow stipulates this, or if I need to stipulate after my SET Sales line. This is my code so far, but unfortunately, it updates all of the offices sales, not just the one that the salesrep belongs to:
CREATE OR REPLACE TRIGGER UpdateOffices
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
UPDATE Offices
SET Sales = Sales + :NEW.Amount
WHERE Office IN (SELECT RepOffice
FROM Salesreps
WHERE RepOffice IS NOT NULL);
End;
/
SHOW ERRORS
Sales is the name of the column on the Offices table. Amount if the name used in the stored proc.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我们已经建议您在 Salesrep 上添加谓词，例如
WHERE Salesrep = :NEW.Rep。为什么不将其添加到子查询中？You've already been advised to add a predicate on Salesreps, e.g.
WHERE Salesrep = :NEW.Rep. Why don't you add it to the subquery?使用此触发方法实施解决方案时，您将遇到各种问题。
一旦订单修改提交到数据库，Office.Sales 列值就会变得过时。例如，当订单的价格或数量发生更改或删除时。
我建议您将此要求实现为“提交时刷新”物化视图。
You are going to run into all sorts of problems implementing your solution using this trigger method.
The Office.Sales column value will become stale as soon as amendments to orders are committed to the database. For example, when the price or quantity on an order is changed or deleted.
I would recommend you implement this requirement as a 'Refresh on Commit' Materialized View.