2018-10-25

Triggers no PostgreSQL

As triggers no PostgreSQL são comandos (funções do PostgreSQL) executados SEMPRE logo após (ou antes) um INSERT, UPDATE ou DELETE em alguma tabela específica. Com esses comandos é possível automatizar completamente seu banco de dados SQL. Ao receber uma inserção em uma tabela, uma trigger tem poder de apagar um dado de outra tabela, por exemplo.

Particularmente gosto de usar ele para automatizar criação de dados no PostgreSQL.

Para fazermos uma trigger funcionar precisamos criar duas entidades no banco de dados:

"Definição da Trigger"

"Trigger Function"

A "Definição da Trigger" define-se em qual tabela será "vigiada", quais operações serão vigiadas (INSERT? UPDATE? DELETE? todos?) e qual função será chamada.

Esta definição envolve 4 parâmetros

nome da tabela em que a trigger operará

operação que acionará a trigger, sendo INSERT, UPDATE, DELETE, TRUNCATE (ou uma combinação dessas operações)

momento de acionamento da trigger, BEFORE (antes de inserir na tabela), AFTER (depois de inserir na tabela), INSTEAD OF (ao invés de inserir na tabela)

se a operação será executada uma vez (FOR EACH STATEMENT) ou várias vezes, uma para cada linha (FOR EACH ROW)

CREATE TRIGGER trigger_tal[BEFORE|AFTER|INSTEAD OF][INSERT, UPDATE, DELETE, TRUNCATE]-- para escolher mais de uma operação dá pra escrever assim:-- INSERT OR UPDATE OR DELETE OR TRUNCATEON schema.tabela_tal[FOR EACH STATEMENT |FOR EACH ROW]EXECUTE PROCEDURE nome_schema.nome_trigger_function();

Atenção! Não colocar schema no nome da "Definição da Trigger" pois ela está amarrada a uma tabela e não exatamente a um schema.

CREATE OR REPLACE TRIGGER não funciona.

CREATE TRIGGER IF NOT EXISTS não funciona também.

Quando você usa o BEFORE, é possível mudar o valor a ser inserido, atualizado ou deletado. A função, em teoria, deveria retornar sempre NEW.

Se você mudar para OLD num update, as colunas nunca serão atualizadas.

Você pode mudar o valor de algum atributo do NEW também, tipo NEW.coluna_x = 1234
return NEW;

Isso fará que sempre seja salvo 1234 na coluna_x

Se você usar o AFTER, isto não acontece.

Em qualquer das circunstâncias, se a trigger lançar uma EXCEPTION, toda a operação fará ROLLBACK.

CREATE TABLE public.test(id SERIAL PRIMARY KEY,value NUMERIC NOT NULL)INSERT INTO test (value)VALUES (234.0)CREATE OR REPLACE FUNCTION public.before_test_trigger()RETURNS TRIGGERLANGUAGE 'plv8'VOLATILEAS $if(NEW.value == 0) plv8.elog(ERROR,"You cannot set value to 0!");//NEW.value = 666;return OLD;$;CREATE OR REPLACE FUNCTION public.after_test_trigger()RETURNS TRIGGERLANGUAGE 'plv8'VOLATILEAS $if(NEW.value == 0) plv8.elog(ERROR,"You cannot set value to 0!");//NEW.value = 666;return OLD;$;CREATE TRIGGER before_testBEFORE UPDATEON public.testFOR EACH ROWEXECUTE PROCEDURE public.before_test_trigger();CREATE TRIGGER after_testAFTER UPDATEON public.testFOR EACH ROWEXECUTE PROCEDURE public.before_test_trigger();