Implement Triggers in Postgre

3

Can you implement a trigger to work in a column that is present in all database tables?

    
asked by anonymous 28.10.2017 / 19:50

1 answer

3

If the tables mentioned do not undergo UPDATE operations, certainly a TRIGGER would not be necessary to solve your problem.

You can change the columns of all tables that contain the date / time by adding a DEFAULT value that would be the current date / time of the system obtained through the function now() :

ALTER TABLE tabela ALTER COLUMN coluna SET DEFAULT now();

For example, the data_acao field of the hypothetical table historico :

-- TABELA ORIGINAL
CREATE TABLE historico
(
    id SERIAL NOT NULL,
    id_usuario INTEGER NOT NULL,
    id_acao INTEGER NOT NULL,
    data_acao TIMESTAMP
);

-- ALTERANDO TABELA ORIGINAL INCLUINDO O VALOR DEFAULT
ALTER TABLE historico ALTER COLUMN data_acao SET DEFAULT now();

-- REGISTRANDO NO HISTORICO AS ACOES DOS USUARIO
INSERT INTO historico ( id_usuario, id_acao ) VALUES ( 100, 1 );
INSERT INTO historico ( id_usuario, id_acao ) VALUES ( 200, 1 );
INSERT INTO historico ( id_usuario, id_acao ) VALUES ( 300, 1 );
INSERT INTO historico ( id_usuario, id_acao ) VALUES ( 100, 2 );
INSERT INTO historico ( id_usuario, id_acao ) VALUES ( 300, 2 );
INSERT INTO historico ( id_usuario, id_acao ) VALUES ( 100, 3 );
INSERT INTO historico ( id_usuario, id_acao ) VALUES ( 200, 3 );

Output with the records of the ações made by usuários that were written to the historico table:

Seeworkingin SQLFiddle

However, if the mentioned tables undergo UPDATE operations, it would be necessary to create a TRIGGER that would be triggered before updating the date / time field ( BEFORE UPDATE ), for example:

CREATE OR REPLACE FUNCTION atualizar_data_hora() RETURNS TRIGGER AS
$BODY$
BEGIN 
    NEW.data_acao := now(); 
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

Since with TRIGGER FUNCTION created, you are able to associate it with as many tables as necessary by creating a TRIGGER , for example:

CREATE TRIGGER trigger_nome BEFORE UPDATE ON tabela FOR EACH ROW EXECUTE PROCEDURE atualizar_data_hora();

See working in SQLFiddle

    
29.10.2017 / 14:33