Implement Triggers in Postgre

Question

It has how to implement a Rigger to work in a column that is present in all tables of the database?

Answer 1

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

You can change the columns of all tables containing the date/time by adding a value DEFAULT which 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 field data_acao 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 );

Exit with the records of ações carried out by usuários which were recorded in the table historico:

See working on Sqlfiddle

However, if the aforementioned tables undergo operations of UPDATE, the creation of a TRIGGER that would be triggered before the date/time field update (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 the 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 on Sqlfiddle