Cómo ejecutar sentencias DDL dentro de un trigger PL/SQL

Supongo que muchos conoceréis el hecho de que no es posible incluir sentencias DDL (es decir, sentencias de definición de objetos como CREATE, REVOKE, GRANT, ALTER, etcétera) dentro de un trigger PL/SQL. Esto es un hecho que tiene una explicación muy sencilla, ¿qué ocurriría si dentro de un trigger ejecutamos una sentencia no transaccional o DDL y necesitamos deshacer la transacción (rollback)? Sencillamente no podríamos deshacer la ejecución de dicha sentencia. En esta situación nuestra transacción dentro del trigger PLSQL no habría tenido lugar, pero la sentencia DDL se habría ejecutado dejando nuestra base de datos Oracle en una situación claramente no deseable.

La ejecución de sentencias no transaccionales en un trigger sólo puede derivar en problemas, así que, en el caso improbable de que necesitemos ejecutar una sentencia DDL dentro de un trigger, mi recomendación es utilizar el paquete PLSQL DBMS_JOB para programar la ejecución de dicha sentencia DDL (CREATE, REPLACE, DROP, etc.).

Veamos un ejemplo:

CREATE TABLE ejecutar_DDL (
njob NUMBER PRIMARY KEY,
sentencia VARCHAR2(2000) );

CREATE OR REPLACE
PROCEDURE ejecutar_DDL_en_trigger
(  p_njob IN NUMBER )
IS
l_ejecutar_DDL ejecutar_DDL%ROWTYPE;
BEGIN
SELECT * INTO l_ejecutar_DDL
FROM ejecutar_DDL
WHERE njob = p_njob;
EXECUTE IMMEDIATE l_ejecutar_DDL.sentencia;
END;

Ahora lo único que queda es, una vez que nuestra transacción en el trigger PL/SQL se ha completado, invocar nuestra sentencia DDL utilizando un bloque PLSQL similar a este:

DECLARE
l_njob NUMBER;
BEGIN
DBMS_JOB.SUBMIT (
l_njob,
'ejecutar_DDL_en_trigger(JOB);' );
INSERT INTO ejecutar_DDL
VALUES (
l_njob,
'...sentencia DDL (sin ; al final)...' );
COMMIT;
END;

De esta forma dispondremos de un procedimiento PL/SQL, ejecutar_DDL_en_trigger, que nos permitirá ejecutar nuestra sentencia DDL recogiendo, de ser necesario, los errores producidos y que, además, se ejecutará poco tiempo después de que la transacción realizada dentro del trigger se haya completado (commit). Y lo mejor de todo es que si nuestra transacción se deshace (rollback), el INSERT en la tabla ejecutar_DDL también se habrá deshecho y la sentencia DDL no se ejecutará.

Conclusión: dentro de un trigger PL/SQL, siempre deberemos crear soluciones semejantes a ésta cuando estemos pensando en ejecutar algo del tipo no transaccional (ver artículo problemas con los triggers PLSQL).