Ayuda plsql y triggers

[ichigohollow]

Un cordial saludo a los foreros de por aca... estoy trabajando con unos triggers y unos store procedures (sp de ahora en adelante) para monitorear unos montos en una tabla.

la cuestion va asi:
tengo una tabla llamada digamos solicitudes y otra saldos_usuarios

sobre solicitudes hay un trigger que cuando una solicitud es rechazada esta llama a un sp que devuelve un saldo al usuario en la tabla saldos_usuarios

sobre saldos_usuarios tengo un trigger que al detectar un aumento de saldo este invoca un sp para que recalcule el saldo de usuario y asi evitar "problemitas"

todo va bien hasta que se decide rechazar una solicitud, alli me da el error de que una tabla esta "mutando", se que esto ocurre pq cuando el sp invocado desde el trigger de la tabla solicitudes retorna el monto al saldo (incremento de saldo por ende recalculo a traves del sp en el trigger de la tabla saldos_usuarios) el recalculo se hace haciendo unos select sobre la tabla solicitudes... y esto es lo que genera el error.

una de las soluciones que se me ocurre es tratar de averiguar si hay una forma de saber desde el trigger de saldos_usuarios si se esta modificando la tabla atraves del trigger de solicitudes para asi decirle que no haga recalculo... pero no se si es posible y me gustaria que por favor me ayudaran

o si alguno de a ustedes se le ocurre alguna otra solucion pues bienvenida sera ... jejeje

muchisimas gracias de antemano por cualquier ayuda que me puedan dar.

[huesos52]

Este tema me parece muy constructivo en cuanto a triggers y el problema de tablas mutantes.
http://www.forosdelweb.com/f100/echa...diendo-786178/

Si sigues con problemas, postea el codigo y lo miramos.

saludos

[ichigohollow]

bueno lo de postear el codigo exacto por estrictas restricciones y leyes y demas cosas de seguridad en mi trabajo no lo puedo hacer (si me pillan a dormir debajo de un puente me tocara jajaja) asi que hare un codigo muy similar al que se usa (y mejor asi ya que los SP son bastante largos ¬_¬)

tabla de solicitudes
campos: id, idusuario, estatus, monto

tabla saldoUsuarios
id(del usuario), disponible, consumido

triggers
create or replace

TRIGGER tri_restaurar_saldo_solicitud
AFTER UPDATE OF estatus
ON solicitudes REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
/* dentro del trigger se invoca a un SP (el cual esta en un paquete) que dentro de su codigo esta lo siguiente*/
SELECT disponible + imonto /*imonto es un parametro de entrada del sp que contiene el monto de la fila modificada en solicitudes */
INTO vmontodisponible
FROM saldoUsuarios
WHERE id = idusuario /*idusuario parametro entrada de la misma naturaleza que imonto*/

UPDATE saldoUsuarios
SET disponible = vmontodisponible
WHERE id = idusuario

create or replace
TRIGGER TRI_MONITOREO_SALDO
BEFORE UPDATE OF
disponible
ON saldosUsuarios
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
/* dentro del trigger se invoca a un SP (el cual esta en un paquete) que dentro de su codigo esta lo siguiente*/
CURSOR VSOLICITUDES (P_IDUSUARIO NUMBER/*ID del usuario de la fila modificada*/) IS
SELECT T.ID, T.MONTO, T.SPKESTATUSSOLICITUD
FROM SOLICITUDES T
WHERE T.IDUSUARIO = P_IDUSUARIO

Como puedes ver aca el si se rechaza una solicitud (update sobre el estatus) el monto de esta tiene que retornar al disponible del usuario (eso es lo que hace el primer trigger) el segundo trigger se ejecutara si y solo si el disponible aumenta, por ende al rechazarse una solicitud el disponible aumente ergo se dispara el segundo trigger y da error por el cursor sobre la tabla "mutante" desencadenadora... (el sp del segundo trigger analiza todas las solicitudes del usuario para ver si el aumento del disponible que se le esta asignando es correcto o no, si hay "mano peluda" o no jejeje).

del link de thread que me recomendaste la solucion in-trhead no aplica para mi problema ya que aquel era que se mandaba a hacer un update sobre la misma tabla que desencadena el trigger cuando lo que tenia que hacer era trabajar con los new y old...

lo de PRAGMA AUTONOMOUS_TRANSACTION; fue interesante lo quise aplicar (y siendo honesto con mucho miedo dada las precauciones dadas) y casi funciona.

se lo aplique al segundo trigger y todo bien hasta que me di cuenta que trabaja con los valores de solicitudes de antes del update, es decir cuando la solicitud aun no ha sido rechazada y por ende calcula mal el disponible. Y eso que el primer trigger es after el segundo trigger sigue tomando el valor de antes del update.

si crees que aun falta codigo como para que entiendas la situacion avisame y agrego mas ... gracias por tu tiempo...

[huesos52]

se lo aplique al segundo trigger y todo bien hasta que me di cuenta que trabaja con los valores de solicitudes de antes del update, es decir cuando la solicitud aun no ha sido rechazada y por ende calcula mal el disponible. Y eso que el primer trigger es after el segundo trigger sigue tomando el valor de antes del update.

Un trigger se comporta como un bloque transaccional. Por ejemplo... no es posible hacer commit ni rollback en la mitad de un trigger. Este siempre será un unico bloque. Si te fijas. el primer trigger se dispara con el evento after... pero solo se verán los cambios reflejados cuando termine su ejecución. El segundo trigger se ejecuta dentro del primero por lo que los cambios no se ven reflejados aún.

El segundo trigger que hace con el cursor? que operaciones DML realiza?

Yo personalmente no estoy deacuerdo con que este tipo de validaciones se hagan haciendo uso de triggers. Yo tendría una función o un procedure que me valide antes del update si el valor que ingresa el usuario es valido y cumple con las características. Este tipo de practicas disminuye el rendimiento de la base de datos.

No se que tan factible sea validar estos datos desde afuera de los triggers,.

[ichigohollow]

Iniciado por huesos52

Un trigger se comporta como un bloque transaccional. Por ejemplo... no es posible hacer commit ni rollback en la mitad de un trigger. Este siempre será un unico bloque. Si te fijas. el primer trigger se dispara con el evento after... pero solo se verán los cambios reflejados cuando termine su ejecución. El segundo trigger se ejecuta dentro del primero por lo que los cambios no se ven reflejados aún.

bueno si lo de los commit y rollback ya lo se y lo del after eso fue lo que me di cuenta, mi esperanza era que el after y el autnomous juntos me ayudaran jejeje...

Iniciado por huesos52

El segundo trigger que hace con el cursor? que operaciones DML realiza?

recuerda que el codigo posteado no esta directo en el trigger sino en un sp que invoca el trigger (que es casi lo mismo.... jejeje) con el cursor me voy estudiando las condiciones de cada solicitud hay otros campos en la tabla solicitud q no coloque pero que se estudian aca, como un campo boleano un, uno de fecha, campo causa y otro condicion los cuales estan asociados entre si, y que dependiendo de ellos se toman decisiones como por ejemplo si tomo el monto asignado a la solicitud o si lo que realmente consumio en esa solicitud el usuario (esto ultimo esta en otra tabla llamada saldosSolicitudes), en conclusion necesito detallar cada solicitud por separado... este segundo trigger hace las siguientes operaciones dml...
select: 4-> la del cursor, 2 para relacionados a tope anuales (una tabla pequeña para ello). y otro sobre una tabla digamos que historica ya q contiene datos de la version anterior del sistema y a veces se necesita datos de ella segun ciertas condiciones.
Inserts: 1-> sobre una tabla de log para informar que habia un error en el disponible.
delete y update: 0

este sp retorna el verdadero disponible y en el trigger se lo asigno al new.disponible

Iniciado por huesos52

Yo personalmente no estoy deacuerdo con que este tipo de validaciones se hagan haciendo uso de triggers. Yo tendría una función o un procedure que me valide antes del update si el valor que ingresa el usuario es valido y cumple con las características. Este tipo de practicas disminuye el rendimiento de la base de datos.

No se que tan factible sea validar estos datos desde afuera de los triggers,.

estas validaciones solo ocurren en determinados eventos, primer trigger solos si el update coloca el estatus en "rechazada"; segundo trigger solo si el disponible aumenta, y como aca se maneja algo delicado como lo que es dinero, se quiere monitorear desde varios flancos que no se alteren saldos ni desde la aplicacion ni desde operaciones directas en el servidor de base de datos (claro esta ultima siempre es mas dificil de controlar completamente).

[jc3000]

Si la memoria no me falla en un trigger si se puede hacer COMMIT, creo que ya lo comenté en un post.

Si haces un bloque DECLARE....... BEGIN...... END y le metes la directiva PRAGMA AUTONOMOUS TRANSACTION si que le puedes meter COMMIT.

Eso si, si no lo controlas bien es peligroso ya que toda la logica dentro de ese bloque se ejecuta "aparte" del resto de codigo del trigger.

[ichigohollow]

Bueno no se si jc3000 sugirió una solucion o si era una aclaratoria en base a lo que comentamos huesos y mi persona anteriormente, pero lo probe e igual nada... ahora una pregunta... yo puedo hacer que un stored procedure le haga como un skip a un trigger, es decir el trigger no se dispare si este procedure toca esa tabla...?

[jc3000]

Era una aclaratoria.

De tu pregunta, no se si colará, pero mediante un EXECUTE IMMEDIATE intenta hacer un disable del trigger desde el procedure.

[ichigohollow]

Iniciado por jc3000

Era una aclaratoria.

De tu pregunta, no se si colará, pero mediante un EXECUTE IMMEDIATE intenta hacer un disable del trigger desde el procedure.

en estos momentos no estoy en el trabajo... no tengo como probarlo pero lo hare a primera hora.... con este comando execute immediate puedo deshabilitar y habilitar el trigger??

lo ultimo q estuve probando antes de la hora de la salida era lo del
sys_context ('USERENV', 'CURRENT_SQL')

para ver si lo que me retornaba pudiese usarlo de alguna manera... pero estuve leyendo que tengo que darle propiedades especiales a la tabla... pero hoy se me agoto el tiempo... probare tanto lo de deshabilitar el trigger como esto (en caso de que ambos me funcionen) le dire al coordinador para que elija entre opciones... porq la tercera via es mucho mas larga y me toca rehacer mucho trabajo... =S

les estare informando mañana como me va... gracias muchachos...

Edit: en la solucion de deshabilitar el trigger con el execute immediate, durante ese periodo de tiempo (que sera corto) alguien mas a parte del sp que esta ejecutando el trigger podra trabajar (escribir en ella) o durante ese momento es exclusiva y las peticiones se ponen en cola?? de no ser exclusiva hay una manera de decir que toda operacion espere hasta que el proceso termine?

[ichigohollow]

bueno muchachos les cuento, de momento me tengo poner en la solucion de deshabilitar el trigger trabajar y habilitar de nuevo

el codigo que tengo planeado hacer es mas o menos asi, y quisiera que con su experiencia en BDs me digan si esta bien o el hecho de por ejemplo uso de multiples execute immediate me dara problemas...

codigo de sp
................
...............
Begin
vsql:='ALTER TRIGGER TRIGGER_SALDO DISABLE';
vlock;='LOCK TABLE saldosUsuarios IN EXCLUSIVE MODE;'
EXECUTE IMMEDIATE vlock;
EXECUTE IMMEDIATE vsql;
sp_recalculo2(idusuario, to_number(to_char(finicio,'yyyy')),idsolicitud, codigoresul, mensajeresul);
IF (CODIGORESUL=1)
THEN
COMMIT;
vsql:='ALTER TRIGGER TRIGGER_SALDO ENABLE';
EXECUTE IMMEDIATE vsql;
ELSE
ROLLBACK;
vsql:='ALTER TRIGGER TRIGGER_SALDO ENABLE';
EXECUTE IMMEDIATE vsql;
END IF;
....
..... resto codigo sp...

mientras escribia el codigo me paso por la mente, si es posible que yo coloque 2 sentencias dentro de la variable string que ejecutara el execute immediate p.e:
vsql:='LOCK TABLE saldosUsuarios IN EXCLUSIVE MODE; ALTER TRIGGER TRIGGER_SALDO DISABLED';
execute immediate vsql;
????