Início / dba / Perguntas / 196072
Accepted
eComEvo
Asked: 2018-01-24 19:09:26 +0800 CST

Acessando o valor da tabela OLD/NEW em uma função de gatilho em SQL simples

  • 772

Estou tentando construir uma função de gatilho para atualizar automaticamente os valores do pedido pai sempre que qualquer um de seus itens for alterado.

Isto é o que eu criei:

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

Quando tento executar isso, recebo este erro:

missing FROM-clause for table "old"

Tentei criar uma referência para a tabela original na 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();

Mas isso só dá o erro syntax error at or near "REFERENCING".

Como faço para obter o da tabela order_idoriginal ?order_items

postgresql functions

1 respostas

  1. Best Answer

    Em primeiro lugar , para criar um gatilho na order_itemstabela, update_totals()deve ser uma função de gatilho que RETURNS TRIGGER( negrito é meu).

    Um gatilho de alteração de dados é declarado como uma função sem argumentos e um tipo de retorno de gatilho . Observe que a função deve ser declarada sem argumentos mesmo que espere receber alguns argumentos especificados em CREATE TRIGGER

    Se não, causa um erro ERROR: function func_order_items must return type trigger.

    Em segundo lugar , lembre-se de que você não pode ter OLDquando estiver AFTER INSERTno gatilho. Se não, ele gera um erro ERROR: record "old" is not assigned yet.

    Por fim , dê uma olhada no exemplo abaixo:

    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

relate perguntas