Hola Visitante

Autor Tema: Monitorizando tablas en OracleSQL (Creando tablas de auditoría)  (Leído 630 veces)

Administación BDL

  • Administrator
  • *****
  • Mensajes: 66
    • Ver Perfil

Buenas noches a todos, hoy os traigo un pequeño código en Oracle SQL que nos ayudara a registrar los cambios que suceden en un conjunto de tablas especificadas dentro de una tabla auxiliar. Esto se conoce como auditoría ya que quedará registrado el cambio del valor antiguo por el valor nuevo y el nombre del campo que ha cambiado.


Cuando tenemos usuarios manazas o algún procedimiento que hace de las suyas. y necesitamos saber que está pasando en cada momento para poder  depurar responsabilidades que está pasando en nuestra preciada base de datos


El primer paso es generar la tabla que almacenara los cambios y crear el trigger que le añadirá el índice de la tabla.




CREATE TABLE AUDITORIA

(

  AUCODI     NUMBER,

  EXTCODI     NUMBER,

  USERID       varchar(255),--Si tenemos codigo de usuario asignado a la tabla habrá que ponerlo

  AUFECHA    DATE,

  AUCAMPO    VARCHAR2(255 CHAR),

  AUANTES    VARCHAR2(255 CHAR),

  AUDESPUES  VARCHAR2(255 CHAR),

)

TABLESPACE [sustituirporesquema]_DATA

PCTUSED    0

PCTFREE    10

INITRANS   1

MAXTRANS   255

STORAGE    (

            INITIAL          64K

            NEXT             1M

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

            BUFFER_POOL      DEFAULT

           )

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;


CREATE OR REPLACE TRIGGER AUDITORIA_TRG

BEFORE INSERT

ON AUDITORIA REFERENCING NEW AS New OLD AS Old

FOR EACH ROW

BEGIN

  :new.AUCODI := AUDITORIA_SEQ.nextval;

END AUDITORIA_TRG;

/


Ya hemos creado la tabla de auditorías que almacenará los cambios. Ahora vayamos al procedimiento que tiene algo más de chicha:



create or replace procedure creadorDeTriggersAutomatico

as

the_cursor     sys_refcursor;

l_column_value varchar2(4000);

trigg varchar2(32000);

   tbls dbms_sql.varchar2_table;

Begin

   tbls(1) := 'TABLA1';

   tbls(2) := 'TABLA2';

   tbls(3) := 'TABLA3';

 --Creamos un array de las tablas que queremos auditar es conveniente

 -- que tengan un campo de indice que se llamen igual para que esto funcione


  FOR i IN tbls.FIRST .. tbls.LAST

   LOOP


   open the_cursor for select t.column_name col

          from user_tab_columns t

        where t.table_name in (tbls(i)); --  tbls(i) nombre  de la tabla


       trigg:='CREATE OR REPLACE TRIGGER trg_'||tbls(i)||'

       BEFORE UPDATE

       ON '||tbls(i)||'

       REFERENCING NEW AS NEW OLD AS OLD

       FOR EACH ROW


   DECLARE

       TYPE tab_col_nt IS TABLE OF VARCHAR2 (30);

       v_tab_col_nt   tab_col_nt;

       v_val_new '||tbls(i)||'%ROWTYPE;

       v_val_old '||tbls(i)||'%ROWTYPE;

    BEGIN

    if updating then

    ';

-- Vamos a generar para cada campo de la tabla una condición

-- de si se esta actualizando la columna insertamos el valor.

      loop


       fetch the_cursor into l_column_value;

        EXIT WHEN the_cursor%NOTFOUND; -- l_column_value nombre del campo de la tabla tbls(i)

        trigg:=trigg||'if updating('''||l_column_value||''' ) AND :OLD.'||l_column_value||' != :NEW.'||l_column_value||'  then


            INSERT INTO AUDITORIA (EXTCODI,userid,aufecha,aucampo,auantes,audespues)

                     VALUES (:OLD.EXTCODI,:NEW.userid,current_date,'''||l_column_value||''',:OLD.'||l_column_value||',:NEW.'||l_column_value||');

            end if;

            ';


     end loop;

      trigg:=trigg||'

        end if;

        end;

      ';

      DBMS_OUTPUT.PUT_LINE(trigg);

end loop;      


end;

/


Ya lo único que nos queda hacer es ejecutar la salida del DBMS y ejecutar lo que nos pone en un gestor de SQL/SQLPlus, cuando hayamos ejecutado esto cualquier cambio que hagamos en una tabla de la lista se verá reflejado.


Si nos fijamos hay una linea un poco especial dentro del trigger: “REFERENCING NEW AS NEW OLD AS OLD”, new y old son dos “pseudorecords” es decir actúan como un rowtype de la tabla especificada pero sólo dentro del contexto del trigger, por lo tanto alguien podría pensar que me complico la existencia y se podría hacer con un execute inmediate pasando el valor del new y old concatenado dentro del texto,..


Pero no es así, como el execute immediate no está dentro del trigger sino en un proceso separado a la hora de compilar el compilador nos avisará la mayoría de veces.. sino lo hará en tiempo de ejecución.


Al ser un pseudorecord (si mal no entendí viene a ser una especie de puntero), oracle tampoco dejará hacer una asignación del pseudorecord a un record.  Me costó varias horas llegar a esta conclusión por no leerme las especificaciones de Oracle, por eso os ahorro el tener que pensar esto ejej


Saludos!!



Nicolas Rodriguez

  • Visitante
Monitorizando tablas en OracleSQL (Creando tablas de auditoría)
« Respuesta #1 en: Junio 29, 2016, 04:20:28 pm »

Buenas tardes amigo , lo he probado con actualizando un registro una tabla de prueba y no me funciona la inserción en la tabla auditoria , es tal cual es código como esta ahí? solo hay que ponerle el valor de la tabla en uno de los valores del array ya ya , pero no me funciona, agradecería tu ayuda.



berni69

  • Visitante
Monitorizando tablas en OracleSQL (Creando tablas de auditoría)
« Respuesta #2 en: Junio 29, 2016, 04:23:33 pm »

Deberias crear la tabla de auditorias y configurar el script para que genere los triggers para las tablas.