在普通 SQL 中訪問觸發器函式中的 OLD/NEW 表值

我正在嘗試建構一個觸發器功能，以在任何項目發生變化時自動更新父訂單值。

這是我創建的：

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表中獲取？

首先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);

引用自：https://dba.stackexchange.com/questions/196072