Registrar cambios en tablas con un trigger genérico

Bueno gente, cuando empecé a investigar este tema ví muchas versiones, algunos que decían que era imposible hacerlo con pgsql, otros que sí o sí había que usar plperl, otros que solo con tablelog, en fin.. los que me conocen saben que cuanto más difícil parece la tarea más ganas me dan de hacerla.
El escenario es el siguiente, tenemos varías tablas en una base de datos, y queremos que cuando se hagan cambios en esa tabla se cree otra tabla que guarde estos cambios.

Bueno gente, cuando empecé a investigar este tema ví muchas versiones, algunos que decían que era imposible hacerlo con pgsql, otros que sí o sí había que usar plperl, otros que solo con tablelog, en fin.. los que me conocen saben que cuanto más difícil parece la tarea más ganas me dan de hacerla.El escenario es el siguiente, tenemos varías tablas en una base de datos, y queremos que cuando se hagan cambios en esa tabla se cree otra tabla que guarde estos cambios.

Bueno, sin mucha más alaraca vamos a los bifes:
Creamos el schema para tener todo ordenadito 😉

CREATE SCHEMA actividad;

Creamos una función que cree la tabla cuando sea necesario:

create or replace function creartablaactividad(tabla TEXT) returns void as
$$
DECLARE
newtable text;
tabla text;
txtquery text;
begin
tabla:=$1;
select tabla || '_actividad' into newtable;
	if not exists(select * from information_schema.tables where table_name = newtable and table_schema = 'actividad') then
    	select 'create table actividad.' ||newtable || ' as select text(''1'') as usuario, current_timestamp as tspam, text(''I'') as operacion, * from ' || tabla ||' limit 1;' into txtquery;
		EXECUTE txtquery;
		select 'delete from actividad.' ||newtable || ';' into txtquery;
		EXECUTE txtquery;
	end if;
end;
$$
language 'plpgsql';

Creamos un trigger genérico para todas las tablas, si bien lo anterior no fue fácil, esto fue lo realmente difícil aunque no lo parezca.

CREATE OR REPLACE FUNCTION process_audit()
  RETURNS trigger AS
$BODY$
DECLARE
newtable text;
col information_schema.columns %ROWTYPE;
txtquery text;
line_old TEXT;
tmpquery text;
i int;
columns_old text[];
BEGIN
select TG_RELNAME || '_actividad' into newtable;
PERFORM creartablaactividad(TG_RELNAME);
        IF (TG_OP = 'DELETE') THEN
			line_old := TRIM( substr(OLD::text,2,(select length(OLD::text)-2)) );
			columns_old := STRING_TO_ARRAY( line_old, ',' );
			i := 0;
			tmpquery := '''' || array_to_string(columns_old, ''',''') || '''';
			tmpquery := replace(tmpquery,','''',',',NULL,');
			SELECT 'INSERT INTO actividad.' || newtable ||' SELECT user, now (), ''D'',' || replace(tmpquery, ',''''',',NULL') into txtquery;
			EXECUTE txtquery;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
			line_old := TRIM( substr(OLD::text,2,(select length(OLD::text)-2)) );
			columns_old := STRING_TO_ARRAY( line_old, ',' );
			i := 0;
			tmpquery := '''' || array_to_string(columns_old, ''',''') || '''';
			tmpquery := replace(tmpquery,','''',',',NULL,');
			tmpquery := replace(tmpquery,','''',',',NULL,');
			SELECT 'INSERT INTO actividad.' || newtable ||' SELECT user, now (), ''ANT'',' || replace(tmpquery, ',''''',',NULL') into txtquery;
			EXECUTE txtquery;
			line_old := TRIM( substr(NEW::text,2,(select length(NEW::text)-2)) );
			columns_old := STRING_TO_ARRAY( line_old, ',' );
			i := 0;
			tmpquery := '''' || array_to_string(columns_old, ''',''') || '''';
			tmpquery := replace(tmpquery,','''',',',NULL,');
			SELECT 'INSERT INTO actividad.' || newtable ||' SELECT user, now (), ''U'',' || replace(tmpquery, ',''''',',NULL') into txtquery;
			EXECUTE txtquery;
           RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
			line_old := TRIM( substr(NEW::text,2,(select length(NEW::text)-2)) );
			columns_old := STRING_TO_ARRAY( line_old, ',' );
			i := 0;
			tmpquery := '''' || array_to_string(columns_old, ''',''') || '''';
			tmpquery := replace(tmpquery,','''',',',NULL,');
			SELECT 'INSERT INTO actividad.' || newtable ||' SELECT user, now (), ''I'',' || replace(tmpquery, ',''''',',NULL') into txtquery;
			EXECUTE txtquery;
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

Lo difícil de esta función es que no se puede usar NEW dentro del select, porque recibiríamos un error como: NEW used in query that is not in a rule

Por lo tanto hay que serilizar el registro y sacarle los parentesis para que quede bien bonito, o sea, la mágia está en: substr(NEW::text,2,(select length(NEW::text)-2))

Creamos ahora el trigger sobre las tablas que queremos supervisar.

CREATE TRIGGER prueba_audit
AFTER INSERT OR UPDATE OR DELETE ON prueba
    FOR EACH ROW EXECUTE PROCEDURE process_audit();

Probamos.
geoprop2=# insert into prueba values (19,19,1);
INSERT 7280876 1
geoprop2=# select * from actividad.prueba_actividad ;
usuario | tspam | operacion | idsegmentoscalle1 | idsegmentoscalle2 | idregiones
----------+-------------------------------+-----------+-------------------+-------------------+------------
postgres | 2010-07-23 18:17:34.155953-07 | I | 19 | 19 | 1
(1 row)
geoprop2=# update prueba set idregiones = 2 where idsegmentoscalle1 = 19 and idsegmentoscalle2 =19;
UPDATE 1
geoprop2=# select * from actividad.prueba_actividad ;
usuario | tspam | operacion | idsegmentoscalle1 | idsegmentoscalle2 | idregiones
----------+-------------------------------+-----------+-------------------+-------------------+------------
postgres | 2010-07-23 18:17:34.155953-07 | I | 19 | 19 | 1
postgres | 2010-07-23 18:18:26.512486-07 | ANT | 19 | 19 | 1
postgres | 2010-07-23 18:18:26.512486-07 | U | 19 | 19 | 2
(3 rows)

geoprop2=# delete from prueba where idsegmentoscalle1 = 19 and idsegmentoscalle2 = 19;
DELETE 1
geoprop2=# select * from actividad.prueba_actividad ;
usuario | tspam | operacion | idsegmentoscalle1 | idsegmentoscalle2 | idregiones
----------+-------------------------------+-----------+-------------------+-------------------+------------
postgres | 2010-07-23 18:21:42.095778-07 | I | 19 | 19 | 1
postgres | 2010-07-23 18:21:46.706143-07 | ANT | 19 | 19 | 1
postgres | 2010-07-23 18:21:46.706143-07 | U | 19 | 19 | 2
postgres | 2010-07-23 18:21:57.163508-07 | D | 19 | 19 | 2
(4 rows)

Espero les guste y sirva 😉

10 thoughts on “Registrar cambios en tablas con un trigger genérico

  1. Excelente aporte, me sirve de mucho… recien estoy iniciando con temas de procedimientos y trigger.

  2. Hola, es muy interesante tu articulo, lo estoy probando pero me da un error cuando ejecuto el trigger especificamente en esta funcion PERFORM creartablaactividad(TG_RELNAME);

    • Hola Johana, debe ser algo en el nombre de tus columnas o en los datos que estás insertanto, por ejemplo un caracter ‘ puede hacer errores. Si porque no pruebas el show warnings, deberías si no hacer un paso a paso con los datos de la función como para ir probando a ver cual es el error. Quizás nombre de columnas con espacios.

      Saludos.

  3. Hola, muy interesante tu manera de realizar una auditoria, pero tengo una pregunta, si por ejemplo quisiera agregar mas informacion a la tabla auditoria, ya obtienes el usuario,la tspam,operacion.

    Pero quisiera obtener por ejemplo: el esquema que es a traves de TG_TABLE_SCHEMA
    o la tabla que es con TG_TABLE_NAME, o la ip de la persona que hizo el registro con la funcion inet_client_addr().

    Se agradece la colaboracion….

    Yo hice que registra esto:
    INSERT INTO auditoria.logged_actions (schema_name,table_name,user_name,action,new_data,query,ip)
    VALUES (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_new_data,inet_client_addr());

Comments are closed.