Como auditar tablas en Postgre 8.2

[r0xdrig0]

Hola amigos, aca yo otra vez, bueno esta vez es para q porfavor me guien en poder auditar o hacer tablas de historiales es decir si elimino los datos completos en mi caso de la tabla niño q estos datos eliminados al cumplir esta condicion q se me guarden en tablas en las cuales yo despues pueda consultar por ejemplo la direccion de todos los niños eliminados o el estado de salud de un niño eliminado, esto lo hago creando tablas al igual q mis tablas q ya tengo (obviamente con otro nombre similar a la tabla a la q le corresponde) o creo vistas para asi consultar en este caso por los niños eliminados??? como puedo/tengo q hacerlo alguien me puede ayudar/guiar porfavor!!!! ojala hallan entendido lo q necesito... de antemano gracias... bye

[huesos52]

Que tal rodrigo

Igualmente con triggers, puedes realizar un sistema de auditoria.
Antes de borrar la información, se inserta los datos en las nuevas tablas acompañadas de la fecha en que se hizo la modificación y el usuario.

Un saludo

[r0xdrig0]

Hola huesos, gracias nuevamente, claro como tu dices eso es, pero mi duda es principalmente si es q para insertar los datos del niño en la tabla de historial, dicha tabla historial la creo igual como cree las demas?? por ejemplo al eliminar los datos de salud del niño q se encuentran por cierto almacenados en la tabla salud_nino, para q me kede el historial de salud guardado tengo q crear una tabla llamada nino_salud, por ejemplo??? para q asi se almacenen los datos borrados desde la tabla salud_nino?? o tengo q crear vistas??? q es lo mas recomendable amigos??? porque tengo claro esa parte de utilizar triggers para q se inserte en la tabla de historial antes de borrarla de la normal, por eso consulto si es q debo crear unas copias de las tablas normales junto con sus relaciones y todo eso.... q me aconsejan amigos? cual es la mejor forma para crear las tablas de auditoria??? agradeceria mucho su ayuda/guia amigos... de antemano gracias.... bye

[huesos52]

Todo depende de la información que quieras guardar como historial despues de borrada.
Lo de las relaciones, no me parece... se puede insertar en una tabla nueva con campos diferentes la información de diversas tablas sin establecer relaciones entre las tablas que almacenaran el historial.

Rodrigo... si nos cuentan los requerimientos que te están haciendo en tu trabajo, tal vez haya una forma mejor y mas eficiente de guardar la información.

Un saludo

[r0xdrig0]

Hola huesos, como siempre tu socorriendome, muchas gracias por eso, bueno en cuanto a lo q me pidieron ahora es q una vez q se elimine a un niño toda la informacion de este se si ga almacenando pero obviamente en otras tablas, es decir cuando elimine a un niño al buscar a este en el menu ver/buscar niños eliminados me aparesca toda la informacion de este su edad direccion.... salud...educacion ... si trabaja o no informacion familiar como; a q familia pertenecia.... etc.... como puedes ver huesos me pidieron mantener todos los datos del niño en un historial, ya el niño q es p fue eliminado en cualquier momento puede ser reingresado, ya q por ejemplo el id de niño es unico y si ese niño se elimina de la bd ese id no se puede asignar a otro niño q no sea el q fue eliminado, como dije ya q en cualquier momento el niño puede ser reingresado por lo q se necesita tener todo el historial de este niño almacenado.... eso es lo q me pidieron ahora.... agradeceria mucho su ayuda/guia amigos, especialmente huesos.... de antemano gracias y espero haber sido claro para explicar lo nuevo q me pidieron.... de antemano gracias amigos... bye

[huesos52]

Nunca he estado de acuerdo con ese tipo de 'soluciones' a problemas que se solucionan con un simple campo. Te explico rodrigo... si la decisión no depende de ti y te están exigiendo hacerlo exclusivamente de esta forma (Borrar los registros y almacenarlos en otra tabla historial), deberías hacer caer en cuenta a tu grupo de analistas el crecimiento innecesario del MER con la inclusión de gran numero de tablas para el almacenamiento de información que posiblemente será reintegrada.

La mejor solución, es incluir en la tabla un campo adicional llamado estado y que tenga 2 tipos de datos, activado y desactivado. En vez de borrar el registro (Delete) hacer una actualización al campo (update).

De esta forma, si se quieren ver los niños, familias, asignaturas, grupos añumnos etc. Solo basta con volver a tener habilitado el registro con un update.

El trabajo que hasta ahora has hecho con los triggers, no se pierde... ya que puedes cambiar las sentencias delete por sentencias update y te ahorraras este problema de historial que tienes en este momento.

Los triggers son muy útiles para auditar tablas, pero para almacenar la fecha y hora en que se hizo un cambio, el usuario que lo realizó etc. pero no para almacenar en otra tabla información que no le estaba haciendo daño a nadie donde estaba y se puede controlar fácilmente con una consulta.

Si sigues empeñado en guardar toda la info en nuevas tablas, creo que si es necesario crear una BD replica con relaciones que permita almacenar los datos borrados en la misma parte, pero como te digo.. no tiene ningún sentido realizar este tipo de implementaciones, cuando existen soluciones mas optimas y sencillas.

Un saludo y espero haberte ayudado.

[r0xdrig0]

Hola huesos, bueno sorry por no haber respondido antes es q he estado ocupado en cosas del mismo sistema, tu solucion me parece ideal y te la agradesco mucho, pero tengo una duda; como lo hago para cuando quieran actualizar los datos del niño??? pq siendo asi el usuario al querer actualizar los datos del niño (de la tabla niño) ahi se pasaran las demas tablas a inactivos?? o como debo hacerlo???? para q cuando actalicen los datos del niño no se cumpla la condicion de q este pase a estado inactivo???..... agradeceria mucho tu ayuda/guia y ojala halla explicado bien mi duda .... de antemano gracias... bye

[huesos52]

rodrigo.. ayudandote un poco del sistema, puedes preguntar que acción se va a tomar y habilitar o deshabilitar los triggers en ese preciso momento.

Con esta sentencia, se deshabilitan los triggers temporalmente mientras se realizan las operaciones que no requieren ser disparadas por el trigger.

Código sql:

UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid = 'nino'::regclass
 

Cuando esto se ejecuta, los triggers dejarán de realizar sus funciones hasta una nueva reactivación con:

Código sql:

UPDATE pg_catalog.pg_class SET reltriggers = 1 WHERE oid = 'nino'::regclass
 

De esa forma, si se van a realizar cambios sobre niño que sean diferentes a la desactivación del niño, se deshabilite el trigger para que no opere la deshabilitación del registro en otras tablas.

[r0xdrig0]

Hola huesos, muchas gracias por tu respuesta, pero tengo una duda; mira como me dices esas lineas son para habilitar o deshabilitar los triggers verdad??, y tambiem me dices q me ayude del sistema para vver q opcion tomar en cuanto a activar o desactivar los triggers, mi duda viene, ya q tengo dos opciones en mi sistema (en cuanto a code php), estas dos son; actualizar los datos niño, con el if ($_POST['Actualizar']) y la otra opcion es cancelar a un niño del sistema if ($_POST['Cancelar']) q esta seria donde actualizo solo el campo estado de la tabla niño y de todas las demas tablas en donde dicho niño tenga participacion verdad.... bueno pero mi duda es a como lo hago en cuanto a codigo sql para activar o desactivar los triggers ayudandome de mi sistema como me lo sugeres tu.... como lo hago?? seria por codigo sql o codigo php para tomar esta desicion de activar o desactivar los triggers con la ayuda de mi sistema???? .... porfavor amigos ruego su ayuda... y espero hallan entendido mi duda.... de antemano gracias.... bye

[huesos52]

Mira esto.
Lo ejecutan desde php.

Para tu sistema, yo vería mas recomendable mantenerlos inactivos. Y que despues del $_post['cancelar'] se habiliten los triggers, realice los cambios y vuelvan a deshabilitarse.

Se entiende la idea?

[r0xdrig0]

Hola huesos, gracias por tu ayuda.... pero te planteo una duda, esta es q q opinas tu si dentro del if($_POST['Modificar'] pongo esto:

Código PHP:

  $UPDATE_DESHABILITA_TRIGGERS="UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid = 'nino'::regclass";
        $ACTUALIZA_DESHABILITA_TRIGGERS = pg_exec ($conn, $UPDATE_DESHABILITA_TRIGGERS); 
   

es recomendable??? me gustaria saber tu opinion please.... de antemano gracias.... bye

[huesos52]

asi está bien rodrigo, sin embargo, si es mas frecuente la modificación de la tabla que la eliminacion de registros de la tabla, sería mejor tenerlo al reves. Tener el trgger por defecto deshabilitado y cuando haya un if($_POST['Modificar'] activar el trigger.

La mejor opción es habilitar/deshabilitar el trgger en el evento que menos concuurrencia tenga en el sistema.

Un saludo.

[r0xdrig0]

Hola amigos, bueno aca yo denuevo ahora con problemas en el momento en q hago el update para q la familia delniño quede inactiva en el momento en q el unico hijo q queda de esa familia es desactivado, bueno el trigger no me tira n error de sintaxis ni nada, pero me manda el error en el momento en q el unico niño de la familia es desactivado (en ese momento la familia de ese niño igual debe quedar inactiva ya q no tiene mas hijos activos dentro del sistema) bueno al ejecutar el update me actualiza el campo estado del niño a DESACTIVADO, en ese momento al ser el unico hijo que queda de la faimila, dicha familia tambien debe quedar desactivada pero aqui me manda el error el trigger al ejecutar el update del campo estado del niño a desactivo, el error q me manda es el siguiente:

Código:

ERROR: schema "ninos" does not exist
Estado SQL:3F000
Contexto:SQL statement "SELECT rut_padre,rut_madre FROM familia WHERE id_familia=ninos.NEW.id_familia"
PL/pgSQL function "cancelar_familia_antes_de_nino" line 28 at SQL statement

y mi trigger es este:

Código:

REATE OR REPLACE FUNCTION cancelar_familia_antes_de_nino() returns TRIGGER AS
  
      $$
   DECLARE
 
       
  
      ninos record;
  
      padres record;
  
         
   
      BEGIN
        
 
      SELECT INTO ninos T1.IF AS id_familia, count(fxh.id_familia) AS numhijos FROM
 
      (SELECT id_familia AS IF FROM familia_x_hijos WHERE id_nino=new.id_nino)AS T1
 
      INNER JOIN familia_x_hijos fxh ON T1.IF = fxh.id_familia
 
      GROUP BY T1.IF;
 
      UPDATE familia_x_hijos SET estado = 'DESACTIVADO' WHERE id_nino=NEW.id_nino;
  
       
  
      IF ninos.numhijos = 1 then  
  
        SELECT INTO padres rut_padre,rut_madre FROM familia WHERE id_familia=ninos.NEW.id_familia;
  
        UPDATE familia SET estado = 'DESACTIVADO' WHERE id_familia=ninos.new.id_familia;
  
        UPDATE padre SET estado = 'DESACTIVADO' WHERE rut_padre=padres.NEW.rut_padre;
  
        UPDATE madre  SET estado = 'DESACTIVADO' WHERE rut_madre=padres.NEW.rut_madre;  
  
      END IF;      
  
      RETURN new;
  
      End;
  
       
 
      $$
 
      LANGUAGE 'plpgsql';
 
      CREATE TRIGGER cancelar_familia_antes_de_nino before
 
         UPDATE ON nino
 
         FOR each row execute procedure cancelar_familia_antes_de_nino();

y no se pq me manda ese error de la variable record ninos, ya q la tengo declarada y al insertar/ejecutar el trigger en la bd no me manda ningun error, en q estoy fallando ahora amigos??? porfavor ruego su ayuda amigos!!!!, de antemano gracias.... bye

[huesos52]

ERROR: schema "ninos" does not exist
Estado SQL:3F000
Contexto:SQL statement "SELECT rut_padre,rut_madre FROM familia WHERE id_familia=ninos.NEW.id_familia"
PL/pgSQL function "cancelar_familia_antes_de_nino" line 28 at SQL statement

ninos y padres son schemas?

Veo que antepones la variable record que creas para hacer referencia al atributo new y old.
Esto es un error de sintaxis.

quita las palabras ninos y padres de las condiciones de las sentencias update y prueba nuevamente.

SELECT rut_padre,rut_madre FROM familia WHERE id_familia=NEW.id_familia

Un saludo rodrigo

[r0xdrig0]

Hola huesos, gracias nuevamente por tu ayuda, pero lo hice como me dijiste mi trigger lo tengo asi ahora:

Código:

 CREATE OR REPLACE FUNCTION cancelar_familia_antes_de_nino() returns TRIGGER AS
  
      $$
   DECLARE
 
       
  
      ninos record;
  
      padres record;
  
         
   
      BEGIN
        
 
      SELECT INTO ninos T1.IF AS id_familia, count(fxh.id_familia) AS numhijos FROM
 
      (SELECT id_familia AS IF FROM familia_x_hijos WHERE id_nino=new.id_nino)AS T1
 
      INNER JOIN familia_x_hijos fxh ON T1.IF = fxh.id_familia
 
      GROUP BY T1.IF;
 
      UPDATE familia_x_hijos SET estado = 'DESACTIVADO' WHERE id_nino=NEW.id_nino;
  
       
  
      IF ninos.numhijos = 1 then  
  
        SELECT rut_padre,rut_madre FROM familia WHERE id_familia=NEW.id_familia;
  
        UPDATE familia SET estado = 'DESACTIVADO' WHERE id_familia=new.id_familia;
  
        UPDATE padre SET estado = 'DESACTIVADO' WHERE rut_padre=NEW.rut_padre;
  
        UPDATE madre  SET estado = 'DESACTIVADO' WHERE rut_madre=NEW.rut_madre;  
  
      END IF;      
  
      RETURN new;
  
      End;
  
       
 
      $$
 
      LANGUAGE 'plpgsql';
 
      CREATE TRIGGER cancelar_familia_antes_de_nino before
 
         UPDATE ON nino
 
         FOR each row execute procedure cancelar_familia_antes_de_nino();

y el error q me da ahora al ejecutar el update del campo estado del niño a desactivado es el siguiente:

Código:

ERROR: record "new" has no field "id_familia"
Estado SQL:42703
Contexto:PL/pgSQL function "cancelar_familia_antes_de_nino" line 28 at SQL statement

el update lo hago en la misma bd asi:

Código:

UPDATE nino SET estado='DESACTIVADO' WHERE nino.id_nino= '0001'

en q estoy fallando ahora amigos??? porfavor de verdad ruego su ayuda amigos!!!... de antemano gracias.... bye

[huesos52]

Ya vi que tratabas de hacer. Ya entendí rl porque anteponias las palabras padres y ninos.

Prueba este trigger.

Código sql:

CREATE OR REPLACE FUNCTION cancelar_familia_antes_de_nino() RETURNS TRIGGER AS
  
      $$
   DECLARE
 
       
  
      ninos record;
  
      padres record;
  
         
   
      BEGIN
        
 
      SELECT INTO ninos T1.IF AS id_familia, COUNT(fxh.id_familia) AS numhijos FROM
 
      (SELECT id_familia AS IF FROM familia_x_hijos WHERE id_nino=NEW.id_nino)AS T1
 
      INNER JOIN familia_x_hijos fxh ON T1.IF = fxh.id_familia
 
      GROUP BY T1.IF;
 
      UPDATE familia_x_hijos SET estado = 'DESACTIVADO' WHERE id_nino=NEW.id_nino;
  
       
  
      IF ninos.numhijos = 1 THEN  
  
        SELECT INTO padres rut_padre,rut_madre FROM familia WHERE id_familia=ninos.id_familia;
  
        UPDATE familia SET estado = 'DESACTIVADO' WHERE id_familia=ninos.id_familia;
  
        UPDATE padre SET estado = 'DESACTIVADO' WHERE rut_padre=padres.rut_padre;
  
        UPDATE madre  SET estado = 'DESACTIVADO' WHERE rut_madre=padres.rut_madre;  
  
      END IF;      
  
      RETURN NEW;
  
      END;
  
       
 
      $$
 
      LANGUAGE 'plpgsql';
 
      CREATE TRIGGER cancelar_familia_antes_de_nino BEFORE
 
         UPDATE ON nino
 
         FOR each ROW EXECUTE PROCEDURE cancelar_familia_antes_de_nino();
 

en q estoy fallando ahora amigos??? porfavor de verdad ruego su ayuda amigos!!!... de antemano gracias.... bye

Rodrigo
Me tomaré unos minutos para explicarte el funcionamiento de las variables record y cual es su utilidad dentro de una función o procedimiento almacenado. Cualquier duda, puedes preguntar nuevamente.

Un trigger se define como un disparador de eventos y es posible acceder a las variables que son ejecutadas en cada sentencia. Por ejemplo estamos hablando de una tabla nino y cuando se inserta en dicha tabla se puede jugar con los valores new.atributo y old.atributo dependiendo del caso.

Para tu caso, es necesario tener otras variables con base en el id_nino, como lo son rut_padre y rut_madre. Como estas variables no son proporcionadas por el evento disparado, es necesario recurrir a las variables record para obtener sus valores. Sin embargo, estas ya no son variables que tienen los atributos new y old para su manipulación. Por esta razón el trigger debe quedar así.

un saludo.

[r0xdrig0]

Hola huesos, te cuento q me funciono bien el trigger ahora si me funciona correctamente, pero ahora tengo otra duda, esta es como debo hacer el mismo trigger pero al reves??? osea cuando ese niño q fue cancelado en el sistema, lo quieran reintegrar como un niño activo.... ya q el update es update nada mas y no distinguira si el campo estado viene actualizado como ACTIVADO o DESACTIVADO... como debo hacer el mismo trigger para esta vez reintegrar como niño activo al menor en el sistema??? osea como identifico q el campo estado viene como ACTIVADO o DESACTIVADO en el update, para en este caso activar uno de los dos triggers (cancelar niño o reintegrar niño) como logro esto?? q debo cambiar en el trigger amigois??? ruego disculpen por tanta molesta en especial a ti hueso q te has portado excelente ayudandome, pero tengo esta duda, ya q debo hacerlo y no se como, para q me distinga si el update viene en ACTIVADO o DESACTIVADO para asi activar uno u otro trigger segun corresponda..... de antemano gracias amigos!!!!.... de verdad ruego su ayuda.... gracias.... bye

[huesos52]

Muy buena pregunta.
Consulto y te cuento.

[r0xdrig0]

Ok huesos de verdad agradeceria mucho tu ayuda.... obviamente estoy/estare averiguando como poder hacerlo, asi como tb estare espectante a tu respuesta..... de verdad agradeceria enormemente tu ayuda.... estare espectante a tu respuesta.... de antemano gracias.... bye

[huesos52]

En mi opinión, deberías tener un unico trigger. Y con un IF establecer las opciones de desactivado y en el else establecer las opciones de activado.

Algo como
if old.estado = 'activado' then
--Codigo de desactivación.
else
--codigo de activación
elsif;

[r0xdrig0]

Hola huesos, probe como me sugeriste, pero no pe compila y no se pq :S

me manda error en la linea del END; no se pq, no se como deberia ser la sintaxis, probe colocando un end; despues de cada end if pero me sigue dando el error q es este

Código:

ERROR: syntax error at or near ";"
Estado SQL:42601
Caracter: 1756

bueno aqui pego el code de mi trigger para q porfavor me echen una mano para saber pq no me compila y como deberia ser..... porfavor amigos ruego su ayuda!!!! mi code del trigger es:

Código:

  CREATE OR REPLACE FUNCTION cancelar_familia_antes_de_nino() returns TRIGGER AS
   
            $$
 
         DECLARE

       
   
            ninos record;
     
 
            padres record;
 
       
 
               
 
         
 
            BEGIN
 
       
 
            SELECT INTO ninos T1.IF AS id_familia, count(fxh.id_familia) AS numhijos FROM
 
       
 
            (SELECT id_familia AS IF FROM familia_x_hijos WHERE id_nino=new.id_nino)AS T1
 
       
 
            INNER JOIN familia_x_hijos fxh ON T1.IF = fxh.id_familia
 
       
 
            GROUP BY T1.IF;
  
       
 
            UPDATE familia_x_hijos SET estado = 'DESACTIVADO' WHERE id_nino=NEW.id_nino;
 
       
 
             
 
       
 
            IF ninos.numhijos = 1 then  
 
                IF nino.old.estado='ACTIVO' then
 
              SELECT INTO padres rut_padre,rut_madre FROM familia WHERE id_familia=ninos.id_familia;
 
       
 
              UPDATE familia SET estado = 'ACTIVO' WHERE id_familia=ninos.id_familia;
 
       
 
              UPDATE padre SET estado = 'ACTIVO' WHERE rut_padre=padres.rut_padre;
 
       
 
              UPDATE madre  SET estado = 'ACTIVO' WHERE rut_madre=padres.rut_madre;  


             ELSE 

                IF nino.old.estado='DESACTIVADO' then


                  UPDATE familia SET estado = 'DESACTIVADO' WHERE id_familia=ninos.id_familia;
 
       
 
                  UPDATE padre SET estado = 'DESACTIVADO' WHERE rut_padre=padres.rut_padre;
 
       
 
                  UPDATE madre  SET estado = 'DESACTIVADO' WHERE rut_madre=padres.rut_madre; 
 
 


            END IF;  
       
  
            END IF;      
  
        
  
            RETURN new;
  
       end;
  
           
  
       
  
             
  
       
  
            $$
  
       
  
            LANGUAGE 'plpgsql';
  
       
  
            CREATE TRIGGER cancelar_familia_antes_de_nino before
  
       
  
               UPDATE ON nino
  
       
  
               FOR each row execute procedure cancelar_familia_antes_de_nino();

porfavor ruego su ayuda amigos!!!! de antemano gracias..... bye

[r0xdrig0]

Porfavor amigos ruego su ayuda!!!! aun no puedo solucionar el problema anterior no se pq me manda ese error de sintaxis plsssssss ruego su ayuda amigos!!!!!!!!!! de antemano gracias..... bye

[cacr]

Hola... ya lo resolviste? te diste cuenta por dónde van los tiros?