PL/SQL使用：new＆amp; ：旧系统，来自DDL中不同表的条件

Question

你好，我对触发器有相关的怀疑， DDL方面，当您对事件进行更新，并且此事件取决于两个表，则可以制作有条件的。

编辑：

问题是： - 可以将不同的表用于表更新的条件吗？ - 可以在表中使用另一个表的新值？ - 我可以在另一个表中使用：旧值吗？ - 可以将另一个表的新列的值用作更新条件吗？ - 我可以使用条件：tablename.columname =：new.tablename.columname吗？

问题在于：

Create or replace trigger TGR_example_consumer
AFTER INSERT OR UPDATE OR DELETE ON CONSUMER
FOR EACH ROW
BEGIN
     IF INSERTING THEN
        UPDATE consumption
            SET amount = amount + :NEW.amount
        WHERE ID_consumption = :NEW.ID_consumption;
        UPDATE TOTAL_consumptions
            SET amount_consumption = amount_consumption + 
               :OLD.consumption.amount;
        WHERE consumption.ID_consumption = :NEW.consumption.ID_consumption; -- > In Here is my dude.       
    ELSIF UPDATING THEN
        UPDATE consumption
            SET amount= (amount+ :OLD.amount) - :NEW.amount
        WHERE ID_consumption = :NEW.ID_consumption;
    ELSE
        UPDATE consumption
            SET amount= amount+ OLD.amount
        WHERE ID_consumption = :NEW.ID_consumption;
    END IF;
END;

在我看来，我的守时很守时，这就是我打开线程的原因。因为我没有看到类似的东西。

编辑： 我希望我已经澄清了这个问题，我想做的是在触发器中另一个表的更新下，自动更新表，几乎像库存或库存控制。

Answer 1

您显示的触发器看起来应该可以运行正常，除了在删除情况下所有：新字段为空的事实。您必须使用：旧字段。另外，您在第四个last行中有old.amount - 应该是：old.amount。另外，在第11行的末尾摆脱半彩色。

Create or replace trigger TGR_example_consumer
AFTER INSERT OR UPDATE OR DELETE ON CONSUMER
FOR EACH ROW
BEGIN
     IF INSERTING THEN
        UPDATE consumption
            SET amount = amount + :NEW.amount
        WHERE ID_consumption = :NEW.ID_consumption;
        UPDATE TOTAL_consumptions
            SET amount_consumption = amount_consumption + 
               :OLD.consumption.amount
        WHERE consumption.ID_consumption = :NEW.consumption.ID_consumption;
    ELSIF UPDATING THEN
        UPDATE consumption
            SET amount= (amount+ :OLD.amount) - :NEW.amount
        WHERE ID_consumption = :NEW.ID_consumption;
    ELSE
        UPDATE consumption
            SET amount= amount+ :OLD.amount
        WHERE ID_consumption = :OLD.ID_consumption;
    END IF;
END;