Cómo ejecutar sentencias DDL dentro un trigger de Oracle

Sunday, January 1, 2012 18:41
Posted in category Oracle

Si necesitas realizar un COMMIT, un SAVEPOINT, una sentecia DDL  (create, alter, …) dentro de un trigger lo más seguro es que recibas algún mensaje de error.

Nada tan sencillo como utilizar la sentencia PRAGMA AUTONOMOUS_TRANSACTION para que la ejecución funcione perfectamente.

Un ejemplo de la sintaxis:

CREATE OR REPLACE TRIGGER XX_TRIGGERNAME_BIR
BEFORE INSERT
ON XXTABLE
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
V_VARIABLE NUMBER;
 
BEGIN
...
COMMIT;
END;
You can leave a response, or trackback from your own site.

4 Responses to “Cómo ejecutar sentencias DDL dentro un trigger de Oracle”

  1. javier says:

    May 29th, 2012 at 4:24 pm

    oye necesito dentro de un disparador crear un usario de sistema, despues de insertar en la tabla de personas que tengo,  es decir por cada persona que tengo en la BD pues debo crearle un usuario a cada una despues de que lo inserto pero no me deja :S  nose si puedas ayudarme  este es mi codigo

    create or replace
    trigger CREAR_USER_ORA_EST before INSERT ON PERSONA
    FOR EACH ROW 
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN 
      create user :new.cod_est identified by :new.login;
    GRANT ESTUDIANTE TO :new.cod_est;
    END;

    Integre la instrucicon de pragma pero el error me sigue diciendo que se encontro el simbolo create cuando se esperaba otro,  es necesario activar algo para que el pragma funcione????

  2. deckerix says:

    May 29th, 2012 at 4:32 pm

    creo que te un “DECLARE”

    create or replace
    trigger CREAR_USER_ORA_EST before INSERT ON PERSONA
    FOR EACH ROW 
    declare
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN 
      create user :new.cod_est identified by :new.login;
    GRANT ESTUDIANTE TO :new.cod_est;
    END;

    Saludos!!!

  3. javier says:

    May 29th, 2012 at 4:43 pm

    gracias por responder, ya le coloque el declare, pero aun asi sigue saliendo la instruccion es necesario activar el pragma o algo por el estilo? nunca lo habia trabajado y aun continua saliendo el error no me deja crear el usuario,  esta instruccion se puede en el oracle 10g express edition?

  4. JenJen says:

    June 14th, 2013 at 6:25 pm

    Sé que es muy tarde mi respuesta pero llegué aquí porque también buscaba la misma solución y la encontré, además si otros también que tienen el mismo problema y se encuentran con esta publicación espero que mi respuesta les sea de ayuda.

    En si la lógica estaba bien .. solo faltaba algunos detalles… que la sentencia lo guardes en una variable y luego la ejecutes.. así por ejemplo:

    CREATE OR REPLACE TRIGGER trg_crear_usuario
    after insert on usuarios 
    for each row
    declare
      PRAGMA AUTONOMOUS_TRANSACTION;
      v_sql varchar2(1000);
    begin
      v_sql:=’CREATE USER ‘||:new.usuario||’ IDENTIFIED BY ‘||:new.contrasenia;
      execute immediate v_sql;
      commit;
    end;

    Por lo que cada que insertes este trigger ejecutara la variable v_sql , que esta tiene la sentencia sql que queremos.
    y Listo!
    (:

Leave a Reply