PostgreSQL 9.6 – Accessing OLD/NEW Table Value in a Trigger Function

functionspostgresqlpostgresql-9.6

I am trying to build a trigger function to automatically update parent order values any time any of it's items changes.

This is what I have created:

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();

When I try to run this, I get this error:

missing FROM-clause for table "old"

I tried creating a reference to the original table in the trigger:

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();

But that just gives the error syntax error at or near "REFERENCING".

How do I get the order_id from the original order_items table?

Best Answer

Firstly, to create a trigger on the order_items table, the update_totals() must be a trigger function which RETURNS TRIGGER (bold is mine).

A data change trigger is declared as a function with no arguments and a return type of trigger. Note that the function must be declared with no arguments even if it expects to receive some arguments specified in CREATE TRIGGER

If not, It causes an error ERROR: function func_order_items must return type trigger.

Secondly, please remember that you cannot have OLD when AFTER INSERT on the trigger. If not, It raises an error ERROR: record "old" is not assigned yet.

Finally, please take a look at the example as below:

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);
Related Question