Como hacer una trigger de auditoría en ORACLE

[cadesa]

Hola a todos, fijense que estoy definiendo una mi pequeña base de datos, y estoy empezando a ver los triggers y se me ocurrio que en cada tabla de mi base tendría que ir un trigger de auditoria en donde cada vez que una persona inserte grabe la información en dos columnas las que he definido como USUARIO_GRABACION, FECHA_GRABACION, y cuando actualice la información se grabe en USUARIO_MODIFICACION Y FECHA_MODIFICACION.

yo se que las fechas las puedo tomar con el "sysdate" pero con el usuario no se como hacerlo porque tengo una tabla en donde estan los datos de los usuarios, usuarios, contraseñas, y otras cosas.

Si alguno de ustedes supiera como hacer esto ayudeme, de verdad se los agradecería.

[kikolice]

para tener el usuario usa "USER", complementando investigate sobre la sentencia "audit" te permite hacer lo que necesitas y mas

[cadesa]

gracias, lo investigaré

[Angubas]

Hola, podrías hacer un trigger que guarde el SqlText, de esta forma:

CREATE OR REPLACE TRIGGER TRIG_PAGO
AFTER DELETE OR INSERT OR UPDATE
ON PAGO -- TABLA A AUDITAR
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
SQ VARCHAR(255);
FC VARCHAR(255);
BEGIN
IF INSERTING THEN
select distinct
vs.sql_text,
to_char(to_date(vs.first_load_time,'YYYY-MM-DD/HH24:MI:SS'),'MM/DD HH24:MI:SS') first_load_time into SQ,FC
from
v$sqlarea vs,
all_users au
where (parsing_user_id != 0)
and (au.user_id(+)=vs.parsing_user_id)
and au.username=user
and first_load_time=(select max(first_load_time)
from v$sqlarea vs ,all_users au
where (parsing_user_id != 0)
and (au.user_id(+)=vs.parsing_user_id)
and au.username=user
and (executions >= 1))
and (executions >= 1);
INSERT INTO AUD_PAGO --TABLA QUE GUARDA LA AUDITORIA
VALUES
(SQ,USER,SYSDATE,USERENV('TERMINAL'));
END IF;
END;

[Angubas]

También puedes crear uno que guarde todo el registro:

CREATE OR REPLACE TRIGGER NOMBRE_TRIGGER
BEFORE INSERT
ON TABLA_A_AUDITAR
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO TABLA_AUDITORIA
(AUD_USER,
AUD_FECHA,
AUD_TIPO,
AUD_TERMINAL)
VALUES
(USER,
SYSDATE,
'INSERT',
USERENV('TERMINAL'));
END IF;
END;

Espero que te sirva.

[kikolice]

Iniciado por Angubas

También puedes crear uno que guarde todo el registro:

CREATE OR REPLACE TRIGGER NOMBRE_TRIGGER
BEFORE INSERT
ON TABLA_A_AUDITAR
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO TABLA_AUDITORIA
(AUD_USER,
AUD_FECHA,
AUD_TIPO,
AUD_TERMINAL)
VALUES
(USER,
SYSDATE,
'INSERT',
USERENV('TERMINAL'));
END IF;
END;

Espero que te sirva.

si mi teoria no me falla

si estas creando un trigguer

BEFORE INSERT

no tendrias la necesidad de identificar que transaccion se esta llevando a cabo

IF INSERTING THEN

siempre caeria ese if, por lo tanto es innecesario

por otro lado no tengo la informacion fresca para confirmar si un BEFORE INSERT se dispara despues de validaciones internas de oracle, como violar llaves primarias, foraneas, etc pero yo usaria un AFTER INSERT para esto

[jc3000]

Me parece ( aunque no estoy seguro ) que da igual si es BEFORE o es AFTER. Primero son las validaciones internas y luego la logica de programación.

[Michelc]

Hola:

-- Usuario que esta conectado a la sesión del equipo
select
rtrim(upper(sys_context('USERENV','OS_USER'))) username,
rtrim(upper(sys_context('USERENV','TERMINAL'))) userterminal
from dual


Saludos,

[Angubas]

Iniciado por kikolice

si mi teoria no me falla

si estas creando un trigguer

BEFORE INSERT

no tendrias la necesidad de identificar que transaccion se esta llevando a cabo

IF INSERTING THEN

siempre caeria ese if, por lo tanto es innecesario

por otro lado no tengo la informacion fresca para confirmar si un BEFORE INSERT se dispara despues de validaciones internas de oracle, como violar llaves primarias, foraneas, etc pero yo usaria un AFTER INSERT para esto

Tienes toda la razon, solo queria mostrarle que podia hacerlo para Update o Delete. Gracias.

[leml1979]

como tomaria el nombre de la tabla y del campo que se modificaron en el caso de un UPDATE, y si es un DELETE que campo tomaria