Transacciones en los procedimientos almacenados

dernesto
19 de Mayo del 2006
Tengo problemas con la manipulación de las transacciones.
Por ejemplo en este SP tengo el objetivo de insertarle un nuevo rol a un usuario de la base de datos pero quiero que el procedimiento completo se realice como una sola transaccion, es decir que si ocurre algun problema no se inserte el registro en la tabla ni se asigne el rol en el servidor.

Esto es lo que hago.

INSERT INTO
DUSUARIOROL
(IDROL, IDUSUARIO) VALUES (prmIDROL, prmIDUSUARIO);
COMMIT;

--////////// SELECCION DEL NOMBRE DE USUARIO Y EL ROL EN EL SERVISDOR DB DEL USUARIO ////----
SELECT t1.nomusuario INTO prmNOMUSUARIO FROM dusuario t1 WHERE t1.idusuario=prmidusuario;

SELECT t1.rolbd INTO prmRol FROM nrol t1 WHERE t1.idrol=prmidrol;


--////////// ASIGNAR ROL EN EL SERVIDOR /////////------
stringroles:='grant '|| prmRol ||' to '||prmNOMUSUARIO;
EXECUTE IMMEDIATE stringroles;
--////////// POR ULTIMO INSERTARLO EN LA TABLA USUARIOROL //////////------

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;

END;

Necesito saber como indicar que todas esas operaciones se realicen todas o no se realicen ninguna, o sea ponerlas todas en una sola transaccion.

Gracias a todos por adelantado.

csogga
19 de Mayo del 2006
Yo lo que haría es el commit al final.
si te falla algo en el medio saldrá por el when others
Estando en la misma sessión registro del insert lo veras sin la necesidad del commit inicial.
Probalo.
Salu2

dernesto
19 de Mayo del 2006
Gracias primero que todo, eso que dices ya lo he probado.
Que es lo que pienso que exista algo de diferente entre estas dos tipos de instrucciones, porque en el caso de agrupar instrucciones como insert, update, delete si me funciona, pero en este caso no.

Pienso que hay algo de diferente con eso del SQL dinamico que estoy utilizando.

Gracias.

Jos?ntonio
19 de Mayo del 2006
Hola,

El rollback del bloque de excepciones sólo sirve para las sentencias DML. Prueba a quitarle los mismos permisos después del rollback. Sería:

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
stringroles:='revoke '|| prmRol ||' from '||prmNOMUSUARIO;
EXECUTE IMMEDIATE stringroles;
END;

Espero te sirva.

Un saludo

car2004
19 de Mayo del 2006
Como bien dice jose Antonio los sentencias DCL no se confirman ni se abortan. Aprate y como curiosidad,
una instrucción DCL como un GRANT hace un Commit sobre todas las sentencias pendientes de confirmar, por lo que te sobraría poner commit, ya que si todo va bien ya lo hace el GRANT.