创建一个触发器，当另一个表中的列被更新时，该触发器将更新一个表中的列 [英] Create a trigger that updates a column on one table when a column in another table is updated

问题描述

我有两个桌子

订单(编号，日期，备注)

Order(id, date, note)

和

交货(编号，备注，日期)

Delivery(Id, Note, Date)

我想创建一个触发器，以在订单"中更新日期时更新交货"中的日期.

I want to create a trigger that updates the date in Delivery when the date is updated in Order.

我当时想做类似

CREATE OR REPLACE TRIGGER your_trigger_name
BEFORE UPDATE
ON Order
DECLARE
BEGIN
   UPDATE Delivery set date = ??? where id = ???
END;

如何获取日期和行ID?

How do I get the date and row id?

谢谢

推荐答案

如何获取日期和行ID?

How do i get the date and row id?

假设这些是您的ORDER表上名为DELIVERY_DATE和ID的列，则触发器应如下所示:

Assuming these are columns on your ORDER table called DELIVERY_DATE and ID your trigger should look something like this:

CREATE OR REPLACE TRIGGER your_trigger_name
    BEFORE UPDATE ON Order
    FOR EACH ROW 
BEGIN
   if :new.delivery_date != :old.delivery_date
   then
       UPDATE Delivery d
       set d.delivery_date = :new.delivery_date
       where d.order_id = :new.id;
    end if;
END;

请注意FOR EACH ROW子句:这是引用单个行中的值所必需的.我使用了IF构造来测试是否在交付时执行UPDATE.如果触发器中没有其他逻辑，则可以这样编写...

Note the FOR EACH ROW clause: that is necessary to reference values from individual rows. I have used an IF construct to test whether to execute the UPDATE on Delivery. If you have no other logic in your trigger you could write it like this...

CREATE OR REPLACE TRIGGER your_trigger_name
    BEFORE UPDATE OF delivery_date ON Order
    FOR EACH ROW 
BEGIN
   UPDATE Delivery d
   set d.delivery_date = :new.delivery_date
   where d.order_id = :new.id;
END;

---

我已经回答了您提出的问题，但是，我要指出，您的数据模型不是最优的.经过适当规范化的设计只能将DELIVERY_DATE保留在一个表上:DELIVERY似乎是一个合乎逻辑的地方.

---

I have answered the question you asked but, as an aside, I will point out that your data model is sub-optimal. A properly normalized design would hold DELIVERY_DATE on only one table: DELIVERY seems teh logical place for it.

这篇关于创建一个触发器，当另一个表中的列被更新时，该触发器将更新一个表中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！