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

Create a trigger that updates a column on one table when a column in another table is updated

提问by GigaPr

i have two tables

我有两张桌子

Order(id, date, note)

订单（ID，日期，备注）

and

和

Delivery(Id, Note, Date)

交货（身、备注、日期）

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;

How do I get the date and row id?

如何获取日期和行 ID？

thanks

谢谢

回答by APC

How do i get the date and row id?

如何获取日期和行 ID？

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

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

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;

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...

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

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;

---

---

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.

我已经回答了您提出的问题，但顺便说一句，我会指出您的数据模型是次优的。正确规范化的设计只会在一个表上保存 DELIVERY_DATE：DELIVERY 似乎是它的合乎逻辑的地方。

回答by Brian

There is an implicit new and old reference in the trigger in the form of: REFERENCING OLD AS OLD NEW AS NEW

触发器中有一个隐式的新旧引用，形式如下： REFERENCING OLD AS OLD NEW AS NEW

You can write to the :NEW value but not to the :OLD value.

您可以写入 :NEW 值，但不能写入 :OLD 值。

UPDATE Delivery set date = :new.delivery_date where id = :new.id;


CREATE OR REPLACE TRIGGER "BUR_TABLENAME" BEFORE
UPDATE ON "TABLE" FOR EACH ROW
BEGIN
  If :new.active_date is not null Then
    :new.active_date := TRUNC(:new.active_date);
End If;
END;

Template:

模板：

CREATE OR REPLACE TRIGGER TRIGGER_NAME
 BEFORE
 UPDATE
 ON TABLE_NAME
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW
DECLARE
   V_VARIABLE   NUMBER (1);
BEGIN
   //Do Stuff;
  null;
end;

回答by Igby Largeman

Use the OLD and NEW bind variables. OLD references the row or column being updated before the change is made; NEW references it after the change.

使用 OLD 和 NEW 绑定变量。OLD 引用更改前正在更新的行或列；NEW 在更改后引用它。

CREATE OR REPLACE TRIGGER trig1
    BEFORE UPDATE
    ON order  REFERENCING NEW AS new
    FOR EACH ROW
BEGIN
    UPDATE delivery
       SET ddate   = :new.ddate
     WHERE id = :new.id;
END;

You can modify the REFERENCINGclause to give your bind variables different names. You can include OLD as <name>too. Example:

您可以修改该REFERENCING子句以赋予绑定变量不同的名称。你也可以包括OLD as <name>。例子：

CREATE OR REPLACE TRIGGER trig1
    BEFORE UPDATE
    ON order REFERENCING OLD AS old_values NEW AS new_values
    ...

If you don't want to change the default names of "old" and "new", you can leave out the REFERENCINGclause completely.

如果您不想更改“旧”和“新”的默认名称，则可以REFERENCING完全省略该子句。

回答by Rene

Whenever there is a need for this kind of trigger, have a good look at your design. Is there really a need for a separate delivery record? Does an order really have more than 1 delivery ?

每当需要这种触发器时，请仔细查看您的设计。真的需要单独的交货记录吗？一个订单真的有超过 1 次交货吗？

Triggers seem nice but they do tend to mess things up pretty quickly.

触发器看起来不错，但它们确实会很快把事情搞砸。