Início / dba / Perguntas / 212135
Accepted
Evgeny Nozdrev
Asked: 2018-07-14 04:17:32 +0800 CST

Como acessar outras linhas INSERTed/UPDATEd do gatilho no PostgreSQL?

  • 772

Solução em MS SQL

As funções de gatilho do MS SQL possuem tabelas deletedde insertedsistema nas quais todas as linhas afetadas pela operação são armazenadas. Você pode contar linhas atualizadas:

set @updatedCount = (select count(*) from deleted)

ou descubra o valor mínimo:

set @updatedMinimumCol1 = (select min(col1) from deleted)

Problema com PostgreSQL

Para FOR EACH ROWtriggers posso usar registros de sistema OLD e NEW, mas eles armazenam apenas 1 linha para cada chamada de trigger. As chamadas do gatilho são separadas, portanto, se o usuário atualizar 10 linhas, o gatilho será chamado 10 vezes, mas a cada vez posso saber apenas cerca de 1 linha atual, não todas as 10 linhas.

Pois FOR EACH STATEMENTnão conheço nenhum mecanismo de acesso a linhas atualizadas. Eu uso o PostgreSQL v9.6 e fui introduzido na v10.OLD TABLENEW TABLE

O PostgreSQL não permite que as tabelas antigas e novas sejam referenciadas em triggers de nível de instrução, ou seja, as tabelas que contêm todas as linhas antigas e/ou novas, que são referenciadas pelas cláusulas OLD TABLE e NEW TABLE no padrão SQL.

Tente com a coluna adicional transaction_timestamp()

Posso adicionar uma coluna especial DEFAULT transaction_timestamp()à tabela principal e usá-la para distinguir as linhas recém-atualizadas de outras, mas não é uma solução, pois várias INSERTs/UPDATEspodem estar em uma transação e elas terão o mesmo carimbo de data e hora da transação. Provavelmente eu poderia limpar essa coluna de carimbo de data/hora no gatilho após cada instrução para evitar esse problema, mas como fazer isso se essa limpeza emitir o gatilho de atualização novamente - será uma chamada de gatilho de atualização infinita.

Portanto, esta tentativa falhou.

Solução ruim no PostgreSQL

A única maneira que eu sei é que:

Primeiro, use FOR EACH ROWo gatilho para coletar estatísticas atuais (min e contagem) como funções agregadas. Eu uso a tabela temporária para armazená-la entre as chamadas (esse gatilho é chamado 1 vez para cada linha). Mas não saberemos qual linha é a última (quando chegará a hora de usar essas estatísticas).

CREATE TEMP TABLE IF NOT EXISTS _stats (
  _current_min   int,
  _current_count int
) ON COMMIT DROP;

IF EXISTS(SELECT 1 FROM _stats LIMIT 1) THEN
  --Current row is not first, there is statistics for previous rows.
  UPDATE _stats
  SET _current_min   = (CASE WHEN NEW.col1 < _current_min THEN NEW.col1
                        ELSE _current_min END)
    , _current_count = _current_count + 1;
ELSE
  --There is no stats because current row is first for this INSERT/UPDATE
  INSERT INTO _stats (_current_min, _current_count)
  VALUES (NEW.col1, 1);
END IF;

Segundo, use FOR EACH STATEMENTo gatilho para usar as estatísticas coletadas. Não se esqueça de limpar a tabela temporária (se o usuário executar vários INSERTs/UPDATEs em uma transação, as estatísticas antigas permanecerão na tabela temporária e corromperão todos os próximos cálculos!).

Para tarefas mais complexas podemos criar tabelas temporárias insertede deletedda mesma forma que _stats.

A solução alternativa

No PostgreSQL podemos usar a cláusula RETURNING para INSERT/UPDATE/DELETE para obter novos valores de todas as linhas afetadas pela operação. Então podemos manipular com eles, mas cada função com INSERTs/UPDATEs tem que implementar essa tecnologia ===> 1. código adicional em funções com tais INSERTs/UPDATEs - duplicação do RETURNING; 2. podemos esquecer de implementar tal tecnologia para nova função; 3. os dados serão corrompidos, pois as manipulações necessárias não serão chamadas automaticamente (como os gatilhos).

A questão

Talvez você conheça uma maneira melhor de acessar todas as linhas afetadas por INSERT/UPDATE?

postgresql trigger

2 respostas

  1. Best Answer

    Veja os documentos , você deve conseguir acessar os registros antigos e novos de um gatilho de instrução:

    CREATE TRIGGER some_table_update_trigger
  AFTER UPDATE ON some_table
  REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab
  FOR EACH STATEMENT
  EXECUTE PROCEDURE do_something_with_newtab_and_oldtab();
    • 7

  2. Para PostgreSQL 10+, veja a resposta de @ewramner .

    Para versões inferiores, encontrei 2 soluções. Ambos funcionam apenas se você deseja usar insertede deletedtabelas na AFTERtrigger.

    Solução 1. Tabelas temporárias _inseridas e _excluídas.

    Primeiro, no BEFORE FOR EACH ROWgatilho crie tabelas temporárias e preencha-as:

    CREATE TRIGGER trigger_fill_sys_tables
  BEFORE INSERT OR UPDATE OR DELETE
  ON public.ttest2
  FOR EACH ROW
EXECUTE PROCEDURE public.tr_fill_sys_tables();


CREATE OR REPLACE FUNCTION public.tr_fill_sys_tables()
  RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  EXECUTE 'CREATE TEMP TABLE IF NOT EXISTS _deleted (LIKE ' || tg_table_schema || '.' || tg_relname || ');';
  IF tg_op <> 'INSERT' THEN
    INSERT INTO _deleted
      SELECT old.*;
  END IF;

  EXECUTE 'CREATE TEMP TABLE IF NOT EXISTS _inserted (LIKE ' || tg_table_schema || '.' || tg_relname || ');';
  IF tg_op <> 'DELETE' THEN
    INSERT INTO _inserted
      SELECT new.*;
  END IF;

  IF tg_op <> 'DELETE' THEN
    RETURN new;
  ELSE
    RETURN old;
  END IF;
END;
$$;

    Via newe oldregistros do sistema, temos acesso ao registro atual cada vez que o gatilho é chamado. Mas quando é chamado para a 1ª linha, não podemos saber sobre a 2ª linha. Não sabemos se existem mais linhas. É por isso

    segundo. No AFTER EACH STATEMENTgatilho todas as linhas já coletadas. Você pode usar estas tabelas:

    CREATE TRIGGER trigger_use_sys_tables
  AFTER INSERT OR UPDATE OR DELETE
  ON ttest2
  FOR EACH STATEMENT
EXECUTE PROCEDURE public.tr_use_sys_tables();


CREATE OR REPLACE FUNCTION public.tr_use_sys_tables()
  RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  _row record;
BEGIN
  --If 0 rows was affected by statement, tr_fill_sys_tables() will NOT be called, _inserted will NOT be created. To avoid a crash, check it:
  IF NOT EXISTS(SELECT 1
                FROM pg_class
                WHERE relname = '_inserted') THEN
    RETURN NULL;
  END IF;

  --Work with sys tables.
  --Note: changing data in them will not affect to main table!
  --Note: changing data in main table can fire this trigger again and fall into infinity loop. CREATE TEMP TABLE _lock() before UPDATE and DROP it after one to check if trigger was called recursively.
  FOR _row IN
  SELECT
      COALESCE(n.id, o.id) AS id
    , o.data               AS old_data
    , n.data               AS new_data
  FROM _inserted n
    FULL OUTER JOIN _deleted o ON n.id = o.id
  LOOP
    RAISE NOTICE 'id = %, old data = %, new data = %', _row.id, _row.old_data, _row.new_data;
  END LOOP;

  --DO NOT FORGET to drop the tables!
  --Just clear is not a solution, since next INSERT/UPDATE/DELETE can work with another table with different structure
  DROP TABLE _deleted;
  DROP TABLE _inserted;

  RETURN NULL;
END;
$$;

    Solução 2. Coluna adicional na tabela.

    Bom se você quiser atualizar as strings inseridas/atualizadas novamente no gatilho. Não funciona para DELETEgatilhos, veja a solução 1.

    Primeiro, adicione a coluna trans_timest timestampà tabela principal.

    Em segundo lugar, escreva transaction_timestamp()para ele por meio de BEFORE FOR EACH ROWgatilhos:

    CREATE TRIGGER trigger_trans_mark
  BEFORE INSERT OR UPDATE
  ON public.ttest
  FOR EACH ROW
EXECUTE PROCEDURE public.tr_ttest_trans_mark();

CREATE OR REPLACE FUNCTION public.tr_ttest_trans_mark()
  RETURNS trigger AS $$
BEGIN
  IF tg_op = 'INSERT' THEN --to not crash when checking "old" record
    new.trans_timest = transaction_timestamp();
  ELSE
    IF old.trans_timest IS NULL THEN --if we are clearing marks, do not set them again
      new.trans_timest = transaction_timestamp();
    END IF;
  END IF;
  RETURN new;
END;
$$
LANGUAGE 'plpgsql';

    Terceiro, você AFTER FOR EACH STATEMENTpode usar essa marca para distinguir as linhas afetadas por ela INSERT/UPDATEdas outras. Não esqueça de limpar as marcas nesta trigger (se o usuário executar vários INSERTs/UPDATEs em uma transação, todos terão o mesmo trans_timest e serão misturados). Mas você pode limpar essas marcas apenas se elas ainda não estiverem limpas (se você chamar UPDATE no gatilho UPDATE, ele chamará a si mesmo - sem essa verificação, você cairá no loop infinito):

    CREATE TRIGGER trigger_use_mark
  AFTER INSERT OR UPDATE
  ON public.ttest
  FOR EACH STATEMENT
EXECUTE PROCEDURE public.tr_ttest_use_mark();

CREATE OR REPLACE FUNCTION public.tr_ttest_use_mark()
  RETURNS trigger AS $$
BEGIN
  IF NOT EXISTS(SELECT 1
            FROM public.ttest t
            WHERE t.trans_timest = transaction_timestamp()
            LIMIT 1) THEN --To avoid infinity loop
    RETURN NULL;
  END IF;

    --Work with marked rows.
    ...

  --DO NOT FORGET to clear marks!
  UPDATE public.ttest
  SET trans_timest = NULL --update this rows again only simultaniously with clearing of marks!
  WHERE trans_timest = transaction_timestamp();

  RETURN NULL;
END;
$$
LANGUAGE 'plpgsql';
    • 2

relate perguntas