11.12.2013

Postgres Triggers

Bueno, he estado probando como es que postgres maneja los famosos triggers alias disparadores para los del idioma español jejejeje, entonces esto empieza asi:


 Para poder trabajar con los disparadores de postgres se supone se puede hacer directamente una operacion de insert, update o delete, pero nunca lo pude hacer asi que decidi seguir bien el manual de postgres y lo pude conseguir.

Me base en los articulos mencionados, para lo que voy a hacer.


Primero se necesita crear una tabla, en mi caso queria crear una tabla con XML, como lo que ya habia mostrado en mi anterior entrada, pero ahora con el sabor de tener una especie de auditoria sobre la tabla creada propiamente desde la BD y asi no molestar con eso desde el programa, iniciemos:


CREATE TABLE IF NOT EXISTS xml_data
(
  i_id_xml int not null,
  c_tabla_xml text not null,
  c_modificado_xml text not null,
  d_modificado_xml date not null,
  xml_data xml not null,
  PRIMARY KEY (i_id_xml, c_tabla_xml)
);
Listo, ahora la tabla de auditoria que casi no puedo sacar, no sabia que campos poner jejeje, pero creo que con la que cree es mas que suficiente:


CREATE TABLE IF NOT EXISTS auditor_xml_data
(
  i_id_auditor bigserial not null,
  d_cambio_auditoria date not null default NOW(),
  i_id_xml int not null,
  c_tabla_xml text not null,
  c_modificado_xml text not null,
  d_modificado_xml date not null,
  xml_data xml not null,
  PRIMARY KEY (i_id_auditor)
);
Listo, ya tengo mi tabla normal y mi tabla de auditoria con la cual, espero poder hacer algo algun dia, para la creacion del trigger, es necesario crear primero una funcion que se retorne de tipo trigger, algo un poco extraño cuando lo lei, y cuando lo  implemente pero funciona que es lo importante jejejeje:

CREATE OR REPLACE FUNCTION auditoriaXML() RETURNS TRIGGER AS $emp_audit$
  DECLARE
  BEGIN
  IF (TG_OP = 'DELETE') THEN
     insert into auditor_xml_data (i_id_xml, c_tabla_xml, c_modificado_xml, d_modificado_xml, xml_data) values (OLD.*);
  ELSIF (TG_OP = 'UPDATE') THEN
     insert into auditor_xml_data (i_id_xml, c_tabla_xml, c_modificado_xml, d_modificado_xml, xml_data) values (OLD.*);
     END IF;
  RETURN NULL;
  END;
$emp_audit$ LANGUAGE plpgsql;
Bueno, ya con eso se da paso al famoso trigger:

create trigger tr_xml_data after update or delete
on xml_data
FOR EACH ROW
EXECUTE PROCEDURE auditoriaXML();

Explicacion:

create trigger: normal es la parte de poder crear el objeto sobre la BD
tr_xml_data : el nombre del trigger
after update or delete: despues de actualizar o eliminar
on xml_data : sobre la tabla xml_data que creamos anteriormente
for each row: En este caso, pense en dejarlo solo con for each statement que era el por defecto, pero pasa lo siguiente: Cuando se hace con el statement no se tiene acceso a la variable OLD que es quien contiene la informacion del row antes de ser cambiada, asi que me toco con esa.
execute procedure auditoriaXML(): Listo con eso le decimos que debe ejecutar la funcion creada de auditoriaXML();

Hice varias pruebas y todo salio bien, lo unico que me preocupa de esto es cuando la tabla xml_data tenga muchos registros, dado que el trigger hace una lectura de la tabla que hayan cambiado y posterior a eso le ejecuta la funcion. Pero para lo que necesito esta bien.


Listo, los dejo con esa inquietud suerte!!


Documentacion:
http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html
http://www.postgresql.org/docs/current/interactive/plpgsql-trigger.html