主页 / dba / 问题 / 196072
Accepted
eComEvo
Asked: 2018-01-24 19:09:26 +0800 CST

在普通 SQL 中访问触发器函数中的 OLD/NEW 表值

  • 772

我正在尝试构建一个触发器功能，以在任何项目发生变化时自动更新父订单值。

这是我创建的：

CREATE OR REPLACE FUNCTION update_totals() RETURNS void AS $$
    UPDATE orders SET
       total_fees = (SELECT SUM(fees) FROM order_items WHERE order_id = OLD.order_id),
       total_profit = (CASE WHEN total_cost IS NOT NULL THEN total - total_tax - total_cost - (SELECT SUM(fees) FROM order_items WHERE order_id = OLD.order_id) ELSE NULL END)
    WHERE id = OLD.order_id;
$$
LANGUAGE SQL;

CREATE TRIGGER update_totals AFTER INSERT 
OR UPDATE OF fees
ON order_items INITIALLY DEFERRED
FOR EACH ROW
WHEN ( OLD.* IS DISTINCT FROM NEW.* ) EXECUTE PROCEDURE update_totals();

当我尝试运行它时，我收到此错误：

missing FROM-clause for table "old"

我尝试在触发器中创建对原始表的引用：

CREATE TRIGGER update_totals AFTER INSERT 
OR UPDATE OF fees
ON order_items
REFERENCING OLD ROW AS old_order
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
WHEN ( OLD.* IS DISTINCT FROM NEW.* ) EXECUTE PROCEDURE update_totals();

但这只是给出了错误syntax error at or near "REFERENCING"。

如何order_id从原始order_items表中获取？

postgresql functions

1 个回答

  1. Best Answer

    首先order_items，要在表上创建触发器，update_totals()必须是触发器函数RETURNS TRIGGER（粗体是我的）。

    数据更改触发器被声明为没有参数且返回类型为 trigger的函数。请注意，该函数必须在 没有参数的情况下声明，即使它希望接收 CREATE TRIGGER 中指定的一些参数

    如果不是，则会导致错误ERROR: function func_order_items must return type trigger。

    其次，请记住，您不能在触发器上设置OLD何时。AFTER INSERT如果不是，则会引发错误ERROR: record "old" is not assigned yet。

    最后，请看下面的例子：

    CREATE TABLE IF NOT EXISTS orders(ID INT NOT NULL PRIMARY KEY, X INT);
CREATE TABLE IF NOT EXISTS order_items (ID INT NOT NULL PRIMARY KEY, ORDER_ID INT, Y INT); 

INSERT INTO orders VALUES(1, 0);
INSERT INTO order_items VALUES(10, 1, 20);
INSERT INTO order_items VALUES(11, 1, 30);

CREATE OR REPLACE FUNCTION func_order_items() RETURNS trigger AS 
$$
BEGIN
  IF (TG_OP = 'UPDATE') THEN
    UPDATE orders
    SET X = (SELECT SUM(Y) FROM order_items WHERE order_id = OLD.order_id)
    WHERE ID = OLD.order_id;
  ELSIF (TG_OP = 'INSERT') THEN
    UPDATE orders
    SET X = (SELECT SUM(Y) FROM order_items WHERE order_id = NEW.order_id)
    WHERE ID = NEW.order_id;
  END IF;
  RETURN NULL;
END
$$
LANGUAGE PLPGSQL;

CREATE TRIGGER trigger_order_items 
AFTER INSERT OR UPDATE 
ON order_items 
FOR EACH ROW EXECUTE PROCEDURE func_order_items();

UPDATE order_items SET Y = 200 WHERE ID = 10;
INSERT INTO order_items VALUES (12, 1, 200);
    • 9

相关问题