Sentencia CREATE TRIGGER

Invocación

Esta sentencia se puede incorporar a un programa de aplicación o emitir mediante el uso de sentencias de SQL dinámico. Es una sentencia ejecutable que puede prepararse de forma dinámica sólo si el comportamiento de ejecución de DYNAMICRULES está en vigor para el paquete (SQLSTATE 42509).

Autorización

Los privilegios de grupo para cualquier tabla o vista especificada en la sentencia CREATE TRIGGER no se tienen en cuenta.

Para sustituir un activador existente, el ID de autorización de la sentencia debe ser el propietario del activador existente (SQLSTATE 42501).

Si se especifica la opción SECURED, los privilegios del ID de autorización de la sentencia deben incluir además la autorización SECADM o CREATE_SECURE_OBJECT (SQLSTATE 42501).

Sintaxis

suceso-activador

acción-activada

sentencia-procedimiento-SQL

Notas:

• ¹ OLD y NEW sólo se pueden especificar una vez cada uno.
• ² OLD TABLE y NEW TABLE sólo se pueden especificar una vez cada uno, y sólo para activadores AFTER o INSTEAD OF.
• ³ FOR EACH STATEMENT no puede especificarse para activadores BEFORE o para activadores INSTEAD OF.
• ⁴ No se debe especificar un suceso de activador más de una vez para la misma operación. Por ejemplo, se permite INSERT OR DELETE, pero no se permite INSERT OR INSERT.
• ⁵ La condición WHEN no puede especificarse para los activadores INSTEAD OF.
• ⁶ Una sentencia de SQL compuesto (compilado) no se puede especificar si la definición de activador incluye una cláusula REFERENCING OLD TABLE o una cláusula REFERENCING NEW TABLE. Una sentencia de SQL compuesto (compilada) no se puede especificar para una definición de activador en un entorno de base de datos particionada.

Descripción

OR REPLACE

Especifica que se debe sustituir la definición del activador si existe uno en el servidor actual. La definición existente se descarta de forma efectiva antes de que se sustituya la nueva definición en el catálogo. Esta opción no se tiene en cuenta si no existe una definición para el activador en el servidor actual. Esta opción sólo puede especificarla el propietario del objeto.

nombre-activador

Indica el nombre del activador. El nombre, incluido en el nombre de esquema implícito o explícito, no debe identificar un activador que ya esté descrito en el catálogo (SQLSTATE 42710). Si se especifica un nombre de dos partes, el nombre de esquema no puede comenzar por SYS (SQLSTATE 42939).

NO CASCADE BEFORE

Especifica que la acción activada asociada se debe aplicar antes de aplicar a la base de datos los cambios ocasionados por la actualización real de la tabla sujeto. También especifica que la acción activada del activador no provocará la activación de otros activadores.

AFTER

Especifica que la acción activada asociada se debe aplicar después de que los cambios ocasionados por la actualización real y la tabla sujeto se apliquen a la base de datos.

INSTEAD OF

Especifica que la acción activada asociada sustituye a la acción que corresponde a la vista sujeto. Sólo está permitido un activador INSTEAD OF para cada tipo de operación de una vista sujeto determinada (SQLSTATE 428FP).

suceso-activador

Especifica que la acción activada que se asocia al activador va a ejecutarse siempre que se aplique uno de los sucesos a la tabla sujeto o a la vista sujeto. Se puede especificar cualquier combinación de sucesos, pero cada suceso (INSERT, DELETE y UPDATE) sólo se puede especificar una vez (SQLSTATE.42613). Si se especifican varios sucesos, la acción activada debe ser una sentencia de SQL compuesto (compilado) (SQLSTATE 42601).

INSERT

Especifica que la acción activada que se asocia al activador va a ejecutarse siempre que se aplique una operación INSERT a la tabla sujeto o a la vista sujeto.

DELETE

Especifica que la acción activada que se asocia al activador va a ejecutarse siempre que se aplique una operación DELETE a la tabla sujeto o a la vista sujeto.

UPDATE

Especifica que la acción activada que se asocia al activador va a ejecutarse siempre que se aplique la operación UPDATE a la tabla sujeto o a la vista sujeto, de acuerdo con las columnas especificadas o implícitas.

Si no se especifica la lista opcional nombre-columna, estarán implicadas todas las columnas de la tabla. Por lo tanto, la omisión de la lista nombre-columna supone la activación del activador cuando se actualiza cualquier columna de la tabla o la vista.

OF nombre-columna,...

Cada nombre-columna especificada debe ser una columna de la tabla base (SQLSTATE 42703). Si se trata de un activador BEFORE, el nombre-columna especificado no puede ser una columna generada distinta de la columna de identidad (SQLSTATE 42989). Ningún nombre-columna puede aparecer más de una vez en la lista nombre-columna (SQLSTATE 42711). El activador sólo se activará mediante la actualización de una columna identificada en la listanombre-columna. Esta cláusula no puede especificarse para un activador INSTEAD OF (SQLSTATE 42613).

ON

nombre-tabla

Designa la tabla sujeto de la definición del activador BEFORE o del activador AFTER. El nombre debe especificar una tabla base o un alias que se resuelva dando como resultado una tabla base (SQLSTATE 42704 ó 42809). El nombre no debe especificar una tabla de catálogos (SQLSTATE 42832), una tabla de consulta materializada (SQLSTATE 42997), una tabla temporal creada, una tabla temporal declarada (SQLSTATE 42995) o un apodo (SQLSTATE 42809).

nombre-vista

Designa la vista sujeto de la definición del activador INSTEAD OF. El nombre debe especificar una vista sin tipo o un alias que se resuelva dando como resultado una vista sin tipo sin columnas de tipo XML (SQLSTATE 42704 o 42809). El nombre no debe especificar una vista de catálogo (SQLSTATE 42832). El nombre no debe especificar una vista que se haya definido utilizando WITH CHECK OPTION (una vista simétrica) o una vista en la que se haya definido una vista simétrica, directa o indirectamente (SQLSTATE 428FQ).

NOT SECURED o SECURED

Especifica si el activador se considera seguro. El valor por omisión es NOT SECURED.

NOT SECURED

Especifica que el activador no se considera seguro.

SECURED

Especifica que el activador se considera seguro. SECURED debe especificarse para un activador cuya tabla sujeto sea una tabla en la que el control de acceso de nivel de fila o de nivel de columna se haya activado (SQLSTATE 428H8). Asimismo, SECURED debe especificarse para un activador que se cree en una vista y una o más de las tablas subyacentes de esa definición de vista tenga el control de acceso de nivel de fila o columna activado (SQLSTATE 428H8).

REFERENCING

Especifica los nombres de correlación para las variables de transición y los nombres de tabla para las tablas de transición. Los nombres de correlación identifican una fila determinada del conjunto de filas que se ven afectadas por la operación SQL activador. Los nombres de tabla identifican todo el conjunto de filas afectadas. Cada fila afectada por la operación SQL activador está disponible para la acción activada mediante la calificación de las columnas con nombres-correlación especificados de la siguiente manera:

OLD AS nombre-correlación

Especifica un nombre de correlación que identifica el estado de fila previo a la operación de SQL de activador.

NEW AS nombre-correlación

Especifica un nombre de correlación que identifica el estado de la fila tal como la ha modificado la operación SQL activador y cualquier sentencia SET de un activador BEFORE que ya se ha ejecutado.

La acción activada dispone del conjunto completo de filas afectadas por la operación SQL activador mediante la utilización de un nombre de tabla temporal que se especifica de la siguiente manera:

OLD TABLE AS identificador

Especifica el nombre de una tabla temporal que identifica los valores del conjunto completo de filas afectadas antes de la operación SQL desencadenante. Si el suceso de activador es INSERT, la tabla temporal está vacía.

NEW TABLE AS identificador

Especifica el nombre de una tabla temporal que identifica el estado del conjunto completo de filas afectadas, tal como las ha modificado la operación SQL desencadenante y mediante cualquier sentencia SET antes de que se haya ejecutado un desencadenante. Si el suceso de activador es DELETE, la tabla temporal está vacía.

Las siguientes normas se aplican a la cláusula REFERENCING:

• Los nombres de la correlación OLD y NEW ni los nombres de OLD TABLE y NEW TABLE no pueden ser idénticos (SQLSTATE 42712).
• Para un activador solamente se puede especificar un nombre-correlación OLD y uno NEW (SQLSTATE 42613).
• Sólo se puede especificar un identificador OLD TABLE y otro NEW TABLE para un activador (SQLSTATE 42613).
• Los identificadores OLD TABLE o NEW TABLE no se pueden definir en un activador BEFORE (SQLSTATE 42898).
• Una variable de transición NEW solamente puede ser el destino de una asignación en una activación BEFORE. De lo contrario, las variables de transición no pueden ser el destino de una asignación (SQLSTATE 42703 o 42987).
• Los nombres de correlación OLD o NEW no se pueden definir en un activador FOR EACH STATEMENT (SQLSTATE 42899).
• Las tablas de transición no pueden modificarse (SQLSTATE 42807).
• El total de las referencias a las columnas de la tabla de transición y a las variables de transición de la acción activada no puede sobrepasar el límite del número de columnas de una tabla o la suma de sus longitudes no puede exceder la longitud máxima de una fila de una tabla (SQLSTATE 54040).
• El ámbito de cada nombre-correlación y de cada identificador es la totalidad de la definición del activador.
• Si la acción activada incluye una sentencia de SQL compuesto (compilado):
  • Los identificadores OLD TABLE o NEW TABLE no se pueden definir.
  • Si la operación es DELETE, el nombre-correlación OLD captura el valor de la fila suprimida. Si la operación es UPDATE, captura el valor de la fila antes de la operación UPDATE. Para una operación de inserción nombre-correlación captura valores nulos para cada columna de una fila.
  • En el caso de una operación de inserción o actualización, el valor de NEW captura el nuevo estado de la fila como lo proporciona la operación original y modificado por cualquier activador BEFORE que se haya ejecutado hasta ese momento. En el caso de una operación de supresión, NEW nombre-correlación captura valores nulos para cada columna de una fila. En un activador BEFORE DELETE, los valores no nulos asignados a las nuevas variables de transición sólo persisten dentro del activador donde ha tenido lugar la asignación.
• Si la acción activada no incluye una sentencia de SQL compuesto (compilado):
  • El nombre-correlación OLD y el identificador OLD TABLE sólo se pueden utilizar si el suceso del activador es una operación DELETE o una operación UPDATE (SQLSTATE 42898). Si la operación es DELETE, el nombre-correlación OLD captura el valor de la fila suprimida. Si la operación es UPDATE, captura el valor de la fila antes de la operación UPDATE. Lo mismo se aplica al identificador OLD TABLE y al conjunto de filas afectadas.
  • El nombre-correlación NEW y el identificador NEW TABLE sólo se pueden utilizar si el suceso del activador es una operación INSERT o una operación UPDATE (SQLSTATE 42898). En ambas operaciones, el valor de NEW captura el nuevo estado de la fila como lo proporciona la operación original y modificado por cualquier activador BEFORE que se haya ejecutado hasta ese momento. Lo mismo se aplica al identificador NEW TABLE y al conjunto de filas afectadas.

FOR EACH ROW

Especifica que la acción activada va a aplicarse una vez para cada fila de la tabla sujeto o de la vista sujeto que se vea afectada por la operación de SQL activador.

FOR EACH STATEMENT

Especifica que la acción activada se debe aplicar una vez para toda la sentencia. Este tipo de granularidad de activador no puede especificarse para un activador BEFORE o para un activador INSTEAD OF (SQLSTATE 42613). Si se especifica, se activará un activador UPDATE o DELETE, aunque no exista ninguna fila que se vea afectada por la sentencia UPDATE o DELETE activador.

acción-activada

Especifica la acción que se debe realizar cuando se activa un activador. Una acción activada se compone de una sentencia-procedimiento-SQL y de una condición opcional para la ejecución de la sentencia-procedimiento-SQL.

Los predicados de suceso de activador se pueden utilizar en cualquier lugar de la acción activada de una sentencia CREATE TRIGGER que utiliza una sentencia de SQL compuesto (compilado) como sentencia-procedimiento-SQL.

WHEN

(condición-búsqueda)

Especifica una condición verdadera, falsa o desconocida. La condición-búsqueda proporciona la posibilidad de determinar si se debe ejecutar o no una determinada acción activada. La acción asociada se realiza sólo si la condición de búsqueda especificada es verdadera. Si se omite la cláusula WHEN, la sentencia-procedimiento-SQL asociada se realiza siempre.

La cláusula WHEN no puede especificarse para los activadores INSTEAD OF (SQLSTATE 42613).

Una referencia a una variable de transición con un tipo de datos XML sólo se puede utilizar en un predicado VALIDATED.

etiqueta:

Especifica la etiqueta para una sentencia de procedimiento de SQL. La etiqueta debe ser exclusiva dentro de una lista de sentencias de procedimiento de SQL, incluidas las sentencias compuestas anidadas dentro de la lista. Tenga en cuenta que las sentencias compuestas que no están anidadas pueden utilizar la misma etiqueta. Varias sentencias de control de SQL admiten la especificación de una lista de sentencias de procedimiento de SQL.

Sólo la sentencia FOR, la sentencia WHILE y la sentencia de SQL compuesto pueden incluir una etiqueta.

sentencia-procedimiento-SQL

Especifica la sentencia de SQL que debe formar parte de la acción activada. No se da soporte a una operación de actualización de búsqueda, supresión de búsqueda, inserción o fusión de apodos en SQL compuesto.

La acción activada de un activador BEFORE en una columna de tipo XML puede invocar la función XMLVALIDATE a través de una sentencia SET, puede dejar los valores de tipo XML sin cambiar o asignarlos a NULL mediante una sentencia SET.

La sentencia-procedimiento-SQL no debe contener una sentencia no soportada (SQLSTATE 42987).

La sentencia-procedimiento-SQL no puede hacer referencia a una variable de transición no definida (SQLSTATE 42703), a un objeto federado (SQLSTATE 42997) o a una tabla temporal declarada (SQLSTATE 42995). o las columnas de inicio y de fin del período BUSINESS_TIME (SQLSTATE 42808).

La sentencia-procedimiento-SQL en un activador BEFORE no puede:

• Contener ninguna operación INSERT, DELETE o UPDATE ni invocar ninguna rutina definida con MODIFIES SQL DATA, si no es de SQL compuesto SQL (compilado).
• Contener ninguna operación DELETE o UPDATE en la tabla sujeto de activador ni invocar ninguna rutina que contenga dichas operaciones, si es de SQL compuesto (compilado).
• Hacer referencia a una tabla de consulta materializada definida con REFRESH IMMEDIATE (SQLSTATE 42997)
• Hacer referencia a una columna generada que no sea la columna de identidad de la variable de transición NEW (SQLSTATE 42989).

Notas

• Al añadir un activador a una tabla que ya contiene filas, no provocará la activación de ninguna acción activada. Así pues, si el activador tiene como objetivo imponer las restricciones de la tabla, es posible que las filas existentes no cumplan dichas restricciones.
• Si los sucesos para dos activadores tienen lugar simultáneamente (por ejemplo, si tienen el mismo suceso, tiempo de activación y tablas sujeto), el primer activador creado es el primero en ejecutarse. Si se utiliza la opción OR REPLACE para sustituir un activador creado anteriormente, la hora de creación cambia y, por lo tanto, puede afectar al orden de ejecución del activador.
• Si se añade una columna a la tabla sujeto cuando ya se han definido los activadores, se aplican las siguientes normas:
  • Si se trata de un activador UPDATE que se ha especificado sin una lista de columnas explícita, cualquier actualización de una columna nueva provocará la activación del activador.
  • La columna no estará visible en la acción activada de cualquier activador definido con anterioridad.
  • Las tablas de transición OLD TABLE y NEW TABLE no contendrán esta columna. Por ese motivo, el resultado de realizar una SELECT * en una tabla de transición no contendrá la columna añadida.
• Si se añade una columna a cualquier tabla a la que se haga referencia en una acción activada, la nueva columna no estará visible para la acción activada.
• Si un objeto al que se hace referencia en el cuerpo del activador no existe o se ha marcado como no válido o el definidor no tiene temporalmente los privilegios para acceder al objeto y, si el parámetro de configuración de la base de datos auto_reval no se ha establecido en DISABLED, el activador se creará satisfactoriamente igualmente. El activador se marcará como no válido y se volverá a validar la siguiente vez que se invoque.
• El resultado de una selección completa especificada en una sentencia-procedimiento-SQL no está disponible dentro ni fuera del activador.
• Un procedimiento que se llama desde una sentencia compuesta activada no debe emitir las sentencias COMMIT ni ROLLBACK (SQLSTATE 42985).
• No se da soporte a un procedimiento que contiene una referencia a un apodo en una sentencia UPDATE de búsqueda, una sentencia DELETE de búsqueda o una sentencia INSERT (SQLSTATE 25000).
• Restricciones de acceso a las tablas: si un procedimiento se ha definido como READS SQL DATA o MODIFIES SQL DATA, ninguna sentencia del procedimiento puede acceder a una tabla que la sentencia compuesta que ha invocado el procedimiento esté modificando (SQLSTATE 57053). Si el procedimiento se ha definido como MODIFIES SQL DATA, ninguna sentencia del procedimiento puede modificar una tabla que la sentencia compuesta que ha invocado el procedimiento esté leyendo o modificando (SQLSTATE 57053).
• Un activador BEFORE DELETE definido en una tabla implicada en un ciclo de restricciones de referencia en cascada no debe incluir referencias a la tabla en la que está definido ni a ninguna otra tabla modificada en cascada durante la evaluación del ciclo de restricciones de integridad de referencia. El resultado de tal activador son datos dependientes y, por lo tanto, tal vez no produzcan resultados coherentes.

  En su forma más simple, significa que un activador BEFORE DELETE de una tabla con una restricción de referencia a sí misma y una norma de supresión CASCADE no debe incluir ninguna referencia a la tabla en la acción-activada.

• La creación de un activador hace que se marquen determinados paquetes como no válidos:
  • Si se crea un activador UPDATE sin una lista de columnas explícita, se invalidan los paquetes que utilizan la actualización en la tabla o la vista de destino.
  • Si se crea un activador UPDATE con una lista de columnas, los paquetes que utilizan la actualización en la tabla de destino sólo se invalidan si el paquete también utiliza la actualización en como mínimo una columna de la lista nombre-columna de la sentencia CREATE TRIGGER.
  • Si se crea un activador INSERT, se invalidan los paquetes que utilizan la inserción en una tabla o vista de destino.
  • Si se crea un activador de supresión, los paquetes que hagan uso de la supresión en la tabla o vista de destino se invalidarán.
• Un paquete permanece invalidado hasta que el programa de aplicación se enlaza explícitamente, se vuelve a enlazar o se ejecuta y el gestor de bases de datos lo vuelve a enlazar de manera automática.
• Activadores no operativos: un activador no operativo es un activador que ya no está disponible y que, por lo tanto, nunca se activará. Un activador deja de ser operativo si:
  • se revoca un privilegio que el creador del activador debe tener para que se ejecute el activador
  • se descarta un objeto, por ejemplo una tabla, una vista o un alias, del que depende la acción activada
  • deja de estar operativa una vista de la que depende la acción activada
  • se descarta un alias que es la tabla sujeto del activador.

  En otras palabras, un activador no operativo es aquél en el que se ha descartado la definición de un activador a consecuencia de las normas en cascada de las sentencias DROP y REVOKE. Por ejemplo, cuando se descarta una vista, los activadores que tienen una sentencia-procedimiento-SQL que contiene una referencia a esa vista se establecen como no operativos.

  Cuando un activador deja de ser operativo, todos los paquetes con sentencias que realicen operaciones y que estuvieran activando el activador quedarán marcados como no válidos. Cuando el paquete se vuelve a enlazar (explícita o implícitamente), se ignora por completo el activador no operativo. De igual forma, las aplicaciones con sentencias de SQL dinámico que realicen operaciones y que estuvieran activando el activador también pasarán por alto por completo todos los activadores que no sean operativos.

  El nombre del activador puede seguirse especificando en las sentencias DROP TRIGGER y COMMENT ON TRIGGER.

  Es posible volver a crear un activador no operativo emitiendo una sentencia CREATE TRIGGER y utilizando el texto de definición del activador no operativo. Este texto de definición de activador se almacena en la columna TEXT de la vista de catálogo SYSCAT.TRIGGERS. Observe que no es necesario descartar de manera explícita el activador no operativo para volver a crearlo. La emisión de una sentencia CREATE TRIGGER con el mismo nombre-activador que un activador no operativo hará que se sustituya dicho activador no operativo con un aviso (SQLSTATE 01595).

  Los activadores no operativos se señalan con una X en la columna VALID de la vista de catálogo SYSCAT.TRIGGERS.

• Errores que se producen al ejecutar activadores: los errores que se producen durante la ejecución de sentencias de SQL activadas se devuelven mediante SQLSTATE 09000 a menos que el error se considere grave. Si el error es grave, se devuelve el SQLSTATE de error grave. El campo SQLERRMC de la SQLCA de un error no grave incluirá el nombre de activador, el SQLCODE, el SQLSTATE y tantos tokens de la anomalía como quepan.

  La sentencia-procedimiento-SQL puede incluir una sentencia SIGNAL SQLSTATE o una función RAISE_ERROR. En ambos casos, la SQLSTATE devuelta es la especificada en la sentencia SIGNAL SQLSTATE o la condición RAISE_ERROR.

• Crear un activador con un nombre de esquema que todavía no existe dará como resultado la creación implícita del esquema siempre y cuando el ID de autorización de la sentencia tenga la autoridad IMPLICIT_SCHEMA. El propietario del esquema es SYSIBM. El privilegio CREATEIN sobre el esquema se otorga a PUBLIC.
• Columna DB2SECURITYLABEL: Se puede hacer referencia a una columna DB2SECURITYLABEL en el cuerpo de activador de BEFORE TRIGGER, pero no se puede cambiar en el cuerpo de un activador BEFORE (SQLSTATE 42989).
• Columnas del período BUSINESS_TIME: Las columnas de inicio y de fin de un período BUSINESS_TIME no se pueden cambiar en el cuerpo de un activador BEFORE UPDATE (SQLSTATE 42808).
• Vistas de sólo lectura: añadir el activador INSTEAD OF para una vista influye en las características de sólo lectura de la vista. Si una vista de sólo lectura tiene una relación de dependencia con un activador INSTEAD OF, el tipo de operación que se define para el activador INSTEAD OF define si se puede suprimir, insertar o actualizar la vista.
• Valores de las variables de transición y activadores INSTEAD OF: los valores iniciales de las nuevas variables de transición o de las nuevas columnas de la tabla de transición que se pueden ver en un activador INSTEAD OF INSERT se establecen como se indica a continuación:
  • Si se especifica explícitamente un valor para una columna en la operación de inserción, la variable de transición nueva correspondiente es dicho valor especificado explícitamente.
  • Si no se especifica explícitamente un valor para una columna en la operación de inserción o se especifica la cláusula DEFAULTS, la variable de transición nueva correspondiente es:
    • El valor por omisión de la columna de tabla subyacente si la columna de vista se puede actualizar (sin el activador INSTEAD OF)
    • De lo contrario, será el valor nulo
  Los valores iniciales de las nuevas variables de transición que se pueden ver en un activador INSTEAD OF UPDATE se establecen como se indica a continuación:
  • Si se especifica un valor explícitamente para una columna de la operación de actualización, la nueva variable de transición correspondiente es dicho valor especificado explícitamente.
  • Si se especifica explícitamente la cláusula DEFAULT para una columna de la operación de actualización, la nueva variable de transición correspondiente es:
    • El valor por omisión de la columna de tabla subyacente si la columna de vista se puede actualizar (sin el activador INSTEAD OF)
    • De lo contrario, será el valor nulo
  • De lo contrario, la nueva variable de transición correspondiente es el valor existente de la columna en la fila.
• Activadores y tablas con tipo: Un activador BEFORE o AFTER puede asociarse a una tabla con tipo en cualquier nivel de una jerarquía de tablas. Si una sentencia de SQL activa varios activadores, estos se ejecutarán en el orden en que fueron creados, aunque estén asociados a tablas diferentes de la jerarquía de tablas con tipo.

  Cuando se activa un activador, sus variables de transición (OLD, NEW, OLD TABLE y NEW TABLE) pueden contener filas de subtablas. Sin embargo, sólo contendrán columnas definidas en la tabla a la que están asociadas.

  Efectos de las sentencias INSERT, UPDATE y DELETE:

  • Activadores de fila: Cuando se utiliza una sentencia de SQL para insertar, actualizar o suprimir una fila de tabla, la sentencia activa activadores de fila asociados a la tabla más específica donde reside la fila, y los activadores asociados a todas las supertablas de esa tabla. Esta norma se cumple siempre, cualquiera que sea la forma en que la sentencia de SQL accede a la tabla. Por ejemplo, al emitir un mandato UPDATE EMP, algunas de las filas actualizadas pueden estar en la subtabla MGR. Para las filas de EMP, se activan los activadores de fila asociados a EMP y a sus supertablas. Para las filas de MGR, se activan los activadores de fila asociados a MGR y a sus supertablas.
  • Activadores de sentencia: Las sentencias INSERT, UPDATE o DELETE activan activadores de sentencia asociados a las tablas (y a sus supertablas) que podrían estar afectados por la sentencia. Esta norma se cumple siempre, con independencia de si hay realmente filas afectadas por la sentencia en esas tablas. Por ejemplo, en un mandato INSERT INTO EMP, se activan los activadores de sentencia para EMP y sus supertablas. Otro ejemplo: En un mandato UPDATE EMP o DELETE EMP, se activan los activadores para EMP y sus supertablas y subtablas, aunque no se haya actualizado ni suprimido ninguna fila de subtabla. Del mismo modo, un mandato UPDATE ONLY (EMP) o DELETE ONLY (EMP) activará activadores de sentencia para EMP y sus supertablas, pero no activadores de sentencia para subtablas.
  Efectos de las sentencias DROP TABLE: Una sentencia DROP TABLE ("descartar tabla") no activa ningún activador asociado a la tabla que se descarta. En cambio, si la tabla descartada es una subtabla, todas las filas de la tabla descartada son elegibles para ser suprimidas de sus supertablas. Por lo tanto, para una tabla T:

  • Activadores de fila: DROP TABLE T activa activadores de supresión de filas que están asociados a todas las supertablas de T, para cada fila de T.
  • Activadores de sentencia: DROP TABLE T activa activadores de supresión de sentencias que están asociados a todas las supertablas de T, sin importar si T contiene o no alguna fila.
  Acciones sobre vistas: Para predecir qué activadores son activados por una acción sobre una vista, utilice la definición de la vista para convertir esa acción en una acción sobre tablas base. Por ejemplo:

  1. Una sentencia de SQL ejecuta UPDATE V1, donde V1 es una vista con tipo y V2 es una subvista de ella. Suponga que V1 deriva de la tabla T1, y V2 deriva de la tabla T2. Esta sentencia podría potencialmente afectar a filas de T1, T2 y de sus subtablas, por lo tanto se activan activadores de sentencia para T1 y T2 y para todas sus subtablas y supertablas.
  2. Una sentencia de SQL ejecuta UPDATE V1, donde V1 es una vista con tipo y V2 es una subvista de ella. Suponga que V1 está definida como SELECT ... FROM ONLY(T1) y V2 está definida como SELECT ... FROM ONLY(T2). Debido a que la sentencia no puede afectar a filas de subtablas de T1 y T2, se activan activadores de sentencia para T1 y T2 y para sus supertablas, pero no para sus subtablas.
  3. Una sentencia de SQL ejecuta UPDATE ONLY(V1), donde V1 es una vista con tipo que está definida como SELECT ... FROM T1. La sentencia puede potencialmente afectar a T1 y a sus subtablas. Por lo tanto, se activan activadores de sentencia para T1 y para todas sus subtablas y supertablas.
  4. Una sentencia de SQL ejecuta UPDATE ONLY(V1), donde V1 es una vista con tipo que está definida como SELECT ... FROM ONLY(T1). En este caso, T1 es la única tabla que puede verse afectada por la sentencia, aunque V1 tenga subvistas y T1 tenga subtablas. Por lo tanto, se activan activadores de sentencia sólo para T1 y sus supertablas.
• Sentencia y activadores MERGE: La sentencia MERGE puede ejecutar operaciones de actualización, supresión e inserción. Los activadores UPDATE, DELETE o INSERT aplicables se activan para la sentencia MERGE cuando se ejecuta una operación de actualización, supresión o inserción.
• Ofuscación: La sentencia CREATE TRIGGER se puede enviar en formato ofuscado. En una sentencia ofuscada, sólo es legible en nombre del activador. El resto de la sentencia se codifica de tal manera que no es legible, pero que el servidor de bases de datos puede descodificar. Las sentencias ofuscadas se pueden generar mediante una llamada a la función DBMS_DDL.WRAP.
• Creación de un activador con la opción SECURED: Por lo general los usuarios con autorización SECADM no tienen privilegios para crear objetos de base de datos como funciones definidas por el usuario o activadores. Suelen examinar los datos a los que accede un activador, comprobar que son seguros y, a continuación, otorgar la autorización CREATE_SECURE_OBJECT a quien ya tenga los privilegios necesarios para crear el activador seguro. Una vez creado el activador, revocarán la autorización CREATE_SECURE_OBJECT al propietario del activador.

  El activador se considera seguro. El gestor de bases de datos considera el atributo SECURED como una aserción que declara que el usuario ha establecido un procedimiento de auditoría para todas las actividades del cuerpo del activador. Si un activador seguro hace referencia a funciones definidas por el usuario, el gestor de base de datos presupone que esas funciones son seguras sin validación. Si esas funciones pueden acceder a datos confidenciales, el usuario con autorización SECADM tiene que asegurarse de que dichas funciones puedan acceder a los datos y de que todas las sentencias ALTER FUNCTION posteriores o los cambios en los paquetes externos sean revisados por este proceso de auditoría.

  Un activador debe ser seguro si su tabla sujeto tiene el control de acceso de nivel de fila o columna activado. Asimismo, un activador debe ser seguro si su tabla sujeto es una vista y una o más de las tablas subyacentes de esa definición de vista tiene el control de acceso de nivel de fila o columna activado.

• Creación de un activador con la opción NOT SECURED: la sentencia CREATE TRIGGER devuelve un error si la tabla sujeto del activador tiene el control de acceso de nivel de fila o columna activado. Asimismo, la sentencia CREATE TRIGGER falla si el activador está definido en una vista y una o más de las tablas subyacentes de esa definición de vista tiene el control de acceso de nivel de fila o columna activado.
• Control de acceso a filas y columnas que no se implanta para las variables de transición y las tablas de transición: los activadores se utilizan para la integridad de la base de datos y, de este modo, se hace necesario el equilibrio entre la seguridad y la integridad de la base de datos. Si el control de acceso de nivel de fila o columna se activa en la tabla sujeto o una tabla subyacente de la vista sujeto, no se aplican los permisos de fila ni las máscaras de columna a los valores iniciales de las variables de transición y las tablas de transición. El control de acceso de nivel de fila y columna que se implanta para la tabla sujeto o una tabla subyacente de la vista sujeto también se ignora para las variables de transición y las tablas de transición a las que se hace referencia en el cuerpo del activador o que se pasan como argumentos a las funciones definidas por el usuario que se invocan en el cuerpo del activador. Para que no exista ningún contratiempo de seguridad para las sentencias de SQL en la acción que el activador realiza para acceder a datos confidenciales en las variables de transición y las tablas de transición, el activador debe crearse con la opción SECURED. Si un activador no es seguro, la sentencia CREATE TRIGGER devuelve un error.
• Consideraciones para columnas ocultas implícitamente: existe una variable de transición para cualquier columna definida como implícitamente oculta. En el cuerpo de un activador, se puede hacer referencia a una variable de transición que se corresponda con una columna oculta implícitamente.
• Volver a vincular paquetes dependientes: Cada activador compilado tiene un paquete dependiente. El paquete se puede volver a vincular en cualquier momento utilizando el procedimiento REBIND_ROUTINE_PACKAGE. Volver a vincular el paquete dependiente no revalida un activador no válido. Revalide un activador no válido utilizando la revalidación automática o utilizando explícitamente el procedimiento ADMIN_REVALIDATE_DB_OBJECTS. La revalidación de activador vuelve a vincular automáticamente el paquete dependiente.

Ejemplos

• Ejemplo 1: cree dos activadores que den como resultado un seguimiento automático del número de empleados que gestiona una empresa. Los activadores interactuarán con las tablas siguientes:
  • La tabla EMPLOYEE con estas columnas: ID, NAME, ADDRESS y POSITION.
  • La tabla COMPANY_STATS con estas columnas: NBEMP, NBPRODUCT y REVENUE.
  El primer activador aumenta el número de empleados cada vez que se da de alta a una persona; es decir, cada vez que se inserta una nueva fila en la tabla EMPLOYEE.

     CREATE TRIGGER NEW_HIRED
       AFTER INSERT ON EMPLOYEE
       FOR EACH ROW
       UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1

  El segundo activador reduce el número de empleados cada vez que un empleado se marcha de la compañía; es decir, cada vez que se suprime una fila de la tabla EMPLOYEE:

     CREATE TRIGGER FORMER_EMP
       AFTER DELETE ON EMPLOYEE
       FOR EACH ROW
       UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1

• Ejemplo 2: cree un activador que asegure que siempre que se actualice un registro de piezas se lleve a cabo la siguiente acción de comprobación y de acción (si es necesaria):
  • Si la cantidad disponible es inferior al 10% de la cantidad máxima de existencias, se emitirá una petición de entrega solicitando el número de artículos de la pieza en cuestión sea la cantidad máxima en existencias menos la cantidad disponible.

  El activador interactuará con la tabla PARTS con estas columnas: PARTNO, DESCRIPTION, ON_HAND, MAX_STOCKED y PRICE.

  ISSUE_SHIP_REQUEST es una función definida por el usuario que envía un formulario de pedido de piezas adicionales a la empresa adecuada.

  
     CREATE TRIGGER REORDER
       AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS
       REFERENCING NEW AS N
       FOR EACH ROW
       WHEN (N.ON_HAND < 0.10 * N.MAX_STOCKED)
       BEGIN ATOMIC
       VALUES(ISSUE_SHIP_REQUEST(N.MAX_STOCKED - N.ON_HAND, N.PARTNO));
       END

• Ejemplo 3: Repita el supuesto del Ejemplo 2, pero utilice una sentencia completa en lugar de una sentencia VALUES para invocar la función definida por el usuario. Este ejemplo también muestra cómo definir el activador como un activador de sentencia en lugar de un activador de fila. Para cada fila de la tabla de transición que se evalúa como verdadera para la cláusula WHERE, se emite una petición de entrega para la pieza.

  
     CREATE TRIGGER REORDER 
        AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS 
        REFERENCING NEW TABLE AS NTABLE
        FOR EACH STATEMENT
           BEGIN ATOMIC 
              SELECT ISSUE_SHIP_REQUEST(MAX_STOCKED - ON_HAND, PARTNO)
                 FROM NTABLE 
              WHERE (ON_HAND < 0.10 * MAX_STOCKED); 
        END

• Ejemplo 4: cree un activador que provoque un error cuando se produzca una actualización que daría como resultado un aumento de salario mayor que el diez por ciento del salario actual.

     CREATE TRIGGER RAISE_LIMIT
       AFTER UPDATE OF SALARY ON EMPLOYEE
       REFERENCING NEW AS N OLD AS O
       FOR EACH ROW
       WHEN (N.SALARY > 1.1 * O.SALARY)
              SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='Salary increase>10%'

• Ejemplo 5: suponga una aplicación que registre y haga seguimientos de los cambios en los precios de las existencias. Esta base de datos contiene dos tablas CURRENTQUOTE y QUOTEHISTORY.

     Tablas: CURRENTQUOTE (SYMBOL, QUOTE, STATUS)
             QUOTEHISTORY (SYMBOL, QUOTE, QUOTE_TIMESTAMP)

  Cuando la columna QUOTE de CURRENTQUOTE se actualiza, la nueva oferta (quote en inglés) debe copiarse con una indicación de fecha y hora, en la tabla QUOTEHISTORY. Asimismo, la columna STATUS de CURRENTQUOTE debería actualizarse para reflejar si las existencias:
  1. son un valor en alza;
  2. están en valor máximo del año;
  3. son un valor a la baja;
  4. están en el valor mínimo del año;
  5. son un valor estable.
  Las sentencias CREATE TRIGGER que realizan este cometido son las siguientes:

  • Definición del activador para determinar el estado:

       CREATE TRIGGER STOCK_STATUS
         NO CASCADE BEFORE UPDATE OF QUOTE ON CURRENTQUOTE
         REFERENCING NEW AS NEWQUOTE OLD AS OLDQUOTE
         FOR EACH ROW
         BEGIN ATOMIC
            SET NEWQUOTE.STATUS =
              CASE
                 WHEN NEWQUOTE.QUOTE >
                       (SELECT MAX(QUOTE) FROM QUOTEHISTORY
                       WHERE SYMBOL = NEWQUOTE.SYMBOL
                       AND YEAR(QUOTE_TIMESTAMP) = YEAR(CURRENT DATE) )
                    THEN 'Alto'
                 WHEN NEWQUOTE.QUOTE <
    (SELECT MIN(QUOTE) FROM QUOTEHISTORY
                       WHERE SYMBOL = NEWQUOTE.SYMBOL
                       AND YEAR(QUOTE_TIMESTAMP) = YEAR(CURRENT DATE) )
                    THEN 'Bajo'
                 WHEN NEWQUOTE.QUOTE > OLDQUOTE.QUOTE
                    THEN 'En alza'
                 WHEN NEWQUOTE.QUOTE < OLDQUOTE.QUOTE
                    THEN 'A la baja'
                 WHEN NEWQUOTE.QUOTE = OLDQUOTE.QUOTE
                    THEN 'Estable'
              END;
         END

  • Definición del activador para registrar un cambio en la tabla QUOTEHISTORY:

    
       CREATE TRIGGER RECORD_HISTORY
         AFTER UPDATE OF QUOTE ON CURRENTQUOTE
         REFERENCING NEW AS NEWQUOTE
         FOR EACH ROW
         BEGIN ATOMIC
           INSERT INTO QUOTEHISTORY
             VALUES (NEWQUOTE.SYMBOL, NEWQUOTE.QUOTE, CURRENT TIMESTAMP);
         END

• Ejemplo 6:cree un activador que altere temporalmente los cambios en el campo de ubicación del registro de empleados de la tabla org. Este activador puede ser útil si se procesan nuevos registros de empleados obtenidos cuando se adquirió una empresa más pequeña y la ubicación de destino asignada al empleado es Toronto y la nueva ubicación de destino es Los Angeles. El activador before asegurará que independientemente del valor que la aplicación asigne para este campo, el valor resultante final será Los Angeles.

  
     CREATE TRIGGER LOCATION_TRIGGER
       NO CASCADE
        BEFORE UPDATE ON ORG
        REFERENCING
          OLD AS PRE
          NEW AS POST
        FOR EACH ROW
        WHEN (POST.LOCATION = 'Toronto')
           SET POST.LOCATION = 'Los Angeles';
        END
  

• Ejemplo 7: cree un activador BEFORE que valide automáticamente los documentos XML que contengan descripciones de productos nuevos antes de que se inserten en la tabla PRODUCT de la base de datos SAMPLE:

  
     CREATE TRIGGER NEWPROD NO CASCADE BEFORE INSERT ON PRODUCT
        REFERENCING NEW AS N
        FOR EACH ROW
        BEGIN ATOMIC
           SET (N.DESCRIPTION) = XMLVALIDATE(N.DESCRIPTION
              ACCORDING TO XMLSCHEMA ID producto);
        END
  

• Ejemplo 8: cree un activador de varios eventos que realice un seguimiento del número y el salario de los empleados que gestiona una empresa. Los activadores interactuarán con las columnas y tablas siguientes:
  • Columnas ID, NAME, ADDRESS, SALARY y POSITION en la tabla EMPLOYEE
  • Columnas NBEMP, NBPRODUCT y REVENUE en la tabla COMPANY_STATS
  El activador incrementa el número de emplea dos cada vez que se contrata a uno nuevo; reduce el número de empleados cuando uno deja la empresa y genera un error si se produce una actualización que supondría un aumento de salario superior al diez por ciento del salario actual:

  CREATE OR REPLACE TRIGGER HIRED
     AFTER INSERT OR DELETE OR UPDATE OF SALARY ON EMPLOYEE
     REFERENCING NEW AS N OLD AS O FOR EACH ROW
     BEGIN
        IF INSERTING THEN UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1;
        ELSEIF DELETING THEN UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1;
        ELSEIF (UPDATING AND (N.SALARY > 1.1 * O.SALARY))
           THEN SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='Salary increase>10%';
        END IF;
     END;

• Ejemplo 9: cree un activador que garantice que se realice la siguiente comprobación y, si es necesario, se actúe en consecuencia, antes de que se actualice un registro de piezas:
  • Si la cantidad disponible es inferior al 10% de la cantidad máxima de existencias, realice un nuevo registro de pedido en la tabla ORDER y emita una petición de entrega solicitando que el número de artículos de la pieza en cuestión sea igual a la cantidad máxima en existencias, menos la cantidad disponible.
  El activador interactúa con las siguientes columnas y tablas:
  • Columnas PARTNO, DESCRIPTION, ON_HAND, MAX_STOCKED y PRICE de la tabla PARTS
  • Columnas PARTNO y PRICE de la tabla ORDER
  ISSUE_SHIP_REQUEST es un procedimiento almacenado de modificación de datos SQL definido por el usuario que envía un formulario de pedido de piezas adicionales a la empresa de suministros y suprime la fila correspondiente de la tabla ORDER cuando la empresa de suministros confirma el formulario de pedido.

  CREATE TRIGGER REORDER
     BEFORE UPDATE OF ON_HAND, MAX_STOCKED ON PARTS
     REFERENCING NEW AS N
     FOR EACH ROW
     WHEN (N.ON_HAND < 0.10 * N.MAX_STOCKED)
     BEGIN
        INSERT INTO ORDERS VALUES (N.MAX_STOCKED - N.ON_HAND, N.PARTNO);
        CALL ISSUE_SHIP_REQUEST(N.MAX_STOCKED - N.ON_HAND, N.PARTNO);
     END;