Início / dba / Perguntas / 339280
Accepted
ymudyruc
Asked: 2024-05-06 22:48:35 +0800 CST

Postgres: ação automática (incrementar contagem de chave estrangeira) após atualização

  • 772

Como automatizar uma atualização em uma tabela, após uma alteração em outra tabela?

Diga (veja abaixo) eu tenho uma mesa countriese uma mesa cities. Como fazer com que, ao adicionar uma cidade na tabela cities, a totalcoluna seja atualizada automaticamente (incrementada para a cidade correspondente) na tabela countries?

Tipo de :

BEGIN; INSERT INTO cities VALUES ('Tokyo', 'Japan'); UPDATE countries SET total=1 WHERE name='Japan'; COMMIT;

, mas automático, faça um countries.the_city.total++ao adicionarcities.the_city .

Países:

postgres=# SELECT * FROM countries;
 name  | total 
-------+-------
 USA   |     0
 Japan |     0
(2 rows)

Cidades:

postgres=# SELECT * FROM cities;
 name | country                                                                                                                                                          
------+---------                                                                                                                                                         
(0 rows)

Comandos:

CREATE DATABASE geo;
\c geo;
CREATE TABLE countries (
    name VARCHAR(15) PRIMARY KEY,
    total int
);
CREATE TABLE cities (
    name VARCHAR(15),
    country VARCHAR(15),
    FOREIGN KEY (country) REFERENCES countries(name)
);

Contexto:

SQL padrão ou Postgres v16

postgresql

1 respostas

  1. Best Answer

    As informações redundantes são melhor mantidas com um gatilho.

    Você deve primeiro certificar-se de que totalpossui um valor padrão razoável:

    ALTER TABLE countries
   ALTER total SET DEFAULT 0,
   ALTER total SET NOT NULL;

    Então, um gatilho para manter a contagem poderia ser assim:

    CREATE FUNCTION count_trig() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
      UPDATE countries SET total = total - 1
      WHERE name = OLD.country;
   END IF;

   IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
      UPDATE countries SET total = total + 1
      WHERE name = NEW.country;
   END IF;

   RETURN coalesce(NEW, OLD);
END;$$;

CREATE TRIGGER count_trig BEFORE INSERT OR UPDATE OR DELETE ON cities
   FOR EACH ROW EXECUTE FUNCTION count_trig();

    Este é apenas um exemplo. Há espaço para melhorias: você pode, por exemplo, ignorar o processamento de atualizações que não modificam o nome do país. Você pode adicionar gatilhos adicionais para tornar a solução mais à prova de balas, como adicionar um gatilho que impeça a atualização, totala menos que a atualização tenha sido chamada por um gatilho.

    • 0

relate perguntas