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

i have two tables

我有两张桌子

Order(id, date, note)

订单（id，date，note）

and

和

Delivery(Id, Note, Date)

交货（Id，注，日期）

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

谢谢

4 个解决方案

#1

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似乎是合乎逻辑的地方。

#2

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引用在更改之前更新的行或列;新的参考改变后。

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 REFERENCING clause to give your bind variables different names. You can include OLD as <name> too. Example:

您可以修改REFERENCING子句以为绑定变量指定不同的名称。您也可以将OLD包含为。例：

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 REFERENCING clause completely.

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

#3

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;

#4

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.

触发器似乎很好但是它们确实很容易搞砸了。

#1