创建一个触发器，当另一个表中的列更新时更新一个表上的列

2022-01-01 00:00:00 sql oracle triggers

我有两张桌子

订单(ID、日期、备注)

Order(id, date, note)

和

交货(ID、备注、日期)

Delivery(Id, Note, Date)

我想创建一个触发器，当 Order 中的日期更新时，它会更新 Delivery 中的日期.

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

我正在考虑做类似的事情

I was thinking to do something like 

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 构造来测试是否在交付时执行更新.如果触发器中没有其他逻辑，则可以这样编写...

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.

相关文章