Can you implement a trigger to work in a column that is present in all database tables?
Can you implement a trigger to work in a column that is present in all database tables?
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