Descripción

CREATE TRIGGER crea un nuevo disparador. CREATE OR REPLACE TRIGGER creará un nuevo disparador o reemplazará un disparador existente. El disparador se asociará con la tabla, vista o tabla foránea especificada y ejecutará la función especificada nombre_función cuando se realicen ciertas operaciones en esa tabla.

Para reemplazar la definición actual de un disparador existente, usa CREATE OR REPLACE TRIGGER, especificando el nombre del disparador existente y su tabla asociada. Todas las demás propiedades se reemplazan.

Se puede especificar que el disparador se ejecute antes de intentar la operación en una fila (antes de comprobar las restricciones y de intentar la operación INSERT, UPDATE o DELETE); o después de completar la operación (después de comprobar las restricciones y de completar la operación INSERT, UPDATE o DELETE); o en lugar de la operación (en el caso de inserciones, actualizaciones o eliminaciones en una vista). Si el disparador se ejecuta antes o en lugar del evento, el disparador puede omitir la operación para la fila actual, o cambiar la fila que se está insertando (solo para operaciones INSERT y UPDATE). Si el disparador se ejecuta después del evento, todos los cambios, incluidos los efectos de otros disparadores, son “visibles” para el disparador.

Un disparador marcado como FOR EACH ROW se invoca una vez por cada fila que modifica la operación. Por ejemplo, un comando DELETE que afecta a 10 filas provocará que los disparadores ON DELETE de la relación destino se ejecuten 10 veces distintas, una por cada fila eliminada. En cambio, un disparador marcado como FOR EACH STATEMENT solo se ejecuta una vez para cualquier operación dada, independientemente de cuántas filas modifique (en particular, una operación que modifique cero filas seguirá dando como resultado la ejecución de los disparadores FOR EACH STATEMENT que correspondan).

Los disparadores que se especifican para ejecutarse INSTEAD OF el evento del disparador deben estar marcados como FOR EACH ROW, y solo se pueden definir en vistas. Los disparadores BEFORE y AFTER en una vista deben estar marcados como FOR EACH STATEMENT.

Además, los disparadores se pueden definir para ejecutarse en TRUNCATE, aunque solo FOR EACH STATEMENT.

La siguiente tabla resume qué tipos de disparadores se pueden utilizar en tablas, vistas y tablas foráneas:

Además, la definición de un disparador puede especificar una condición booleana WHEN, que se evaluará para ver si el disparador debe ejecutarse. En los disparadores a nivel de fila, la condición WHEN puede examinar los valores antiguos y/o nuevos de las columnas de la fila. Los disparadores a nivel de sentencia también pueden tener condiciones WHEN, aunque esta característica no es tan útil para ellos, ya que la condición no puede hacer referencia a ningún valor de la tabla.

Si se definen varios disparadores del mismo tipo para el mismo evento, se ejecutarán en orden alfabético por nombre.

Cuando se especifica la opción CONSTRAINT, este comando crea un disparador de restricción (constraint trigger). Es lo mismo que un disparador normal, excepto que el momento de ejecución del disparador se puede ajustar usando SET CONSTRAINTS. Los disparadores de restricción deben ser disparadores AFTER ROW en tablas comunes (no tablas foráneas). Pueden ejecutarse al final de la sentencia que causa el evento disparador, o al final de la transacción contenedora; en este último caso se dice que están diferidos. La ejecución de un disparador diferido pendiente también se puede forzar para que ocurra inmediatamente usando SET CONSTRAINTS. Se espera que los disparadores de restricción generen una excepción cuando se violen las restricciones que implementan.

La opción REFERENCING habilita la recopilación de relaciones de transición, que son conjuntos de filas que incluyen todas las filas insertadas, eliminadas o modificadas por la sentencia SQL actual. Esta característica permite al disparador ver una vista global de lo que hizo la sentencia, no solo una fila a la vez. Esta opción solo se permite para disparadores AFTER en tablas comunes (no tablas foráneas). El disparador no debe ser un disparador de restricción. Además, si el disparador es un disparador UPDATE, no debe especificar una lista de nombre_columna cuando use esta opción. OLD TABLE solo se puede especificar una vez, y solo para un disparador que se pueda ejecutar en UPDATE o DELETE; crea una relación de transición que contiene las imágenes previas (before-images) de todas las filas actualizadas o eliminadas por la sentencia. Del mismo modo, NEW TABLE solo se puede especificar una vez, y solo para un disparador que se pueda ejecutar en UPDATE o INSERT; crea una relación de transición que contiene las imágenes posteriores (after-images) de todas las filas actualizadas o insertadas por la sentencia.

SELECT no modifica ninguna fila, por lo que no puedes crear disparadores SELECT. Las reglas (rules) y las vistas pueden proporcionar soluciones viables a los problemas que parecen necesitar disparadores de tipo SELECT.

Consulta Chapter 37 para obtener más información sobre disparadores.

Parámetros

Notas

Para crear o reemplazar un disparador en una tabla, el usuario debe tener el privilegio TRIGGER en la tabla. El usuario también debe tener el privilegio EXECUTE en la función del disparador.

Usa DROP TRIGGER para eliminar un disparador.

La creación de un disparador a nivel de fila en una tabla particionada provocará la creación de un disparador “clon” idéntico en cada una de sus particiones existentes; y cualquier partición que se cree o acople más tarde también tendrá un disparador idéntico. Si ya existe un disparador con nombre en conflicto en una partición hija, se produce un error a menos que se utilice CREATE OR REPLACE TRIGGER, en cuyo caso ese disparador se reemplaza con un disparador clon. Cuando una partición se desacopla de su padre, sus disparadores clon se eliminan.

Un disparador específico de columna (uno definido mediante la sintaxis UPDATE OF nombre_columna) se ejecutará cuando cualquiera de sus columnas figuren como destinos en la lista SET del comando UPDATE. Es posible que el valor de una columna cambie incluso cuando el disparador no se ejecute, ya que no se consideran los cambios realizados en el contenido de la fila por los disparadores BEFORE UPDATE. Por el contrario, un comando como UPDATE ... SET x = x ... ejecutará un disparador en la columna x, aunque el valor de la columna no haya cambiado.

En un disparador BEFORE, la condición WHEN se evalúa justo antes de que la función se ejecute o se fuera a ejecutar, por lo que usar WHEN no es materialmente diferente de probar la misma condición al comienzo de la función del disparador. Ten en cuenta en particular que la fila NEW vista por la condición es el valor actual, posiblemente modificado por disparadores anteriores. Además, no se permite que la condición WHEN de un disparador BEFORE examine las columnas del sistema de la fila NEW (como ctid), porque estas aún no se habrán establecido.

En un disparador AFTER, la condición WHEN se evalúa justo después de que ocurra la actualización de la fila y determina si se encola un evento para ejecutar el disparador al final de la sentencia. Por lo tanto, cuando la condición WHEN de un disparador AFTER no devuelve true, no es necesario encolar un evento ni volver a obtener la fila al final de la sentencia. Esto puede resultar en aceleraciones significativas en sentencias que modifican muchas filas, si el disparador solo necesita ejecutarse para unas pocas filas.

En algunos casos es posible que un solo comando SQL ejecute más de un tipo de disparador. Por ejemplo, un INSERT con una cláusula ON CONFLICT DO UPDATE puede causar operaciones tanto de inserción como de actualización, por lo que ejecutará ambos tipos de disparadores según sea necesario. Las relaciones de transición suministradas a los disparadores son específicas de su tipo de evento; por lo tanto, un disparador INSERT solo verá las filas insertadas, mientras que un disparador UPDATE solo verá las filas actualizadas.

Las actualizaciones o eliminaciones de filas causadas por acciones de cumplimiento de claves foráneas, como ON UPDATE CASCADE o ON DELETE SET NULL, se tratan como parte del comando SQL que las causó (ten en cuenta que tales acciones nunca se difieren). Se ejecutarán los disparadores correspondientes en la tabla afectada, de modo que esto proporciona otra forma en la que un comando SQL podría ejecutar disparadores que no coinciden directamente con su tipo. En casos sencillos, los disparadores que solicitan relaciones de transición verán todos los cambios causados en su tabla por un solo comando SQL original como una única relación de transición. Sin embargo, hay casos en los que la presencia de un disparador AFTER ROW que solicita relaciones de transición provocará que las acciones de cumplimiento de clave foránea desencadenadas por un único comando SQL se dividan en varios pasos, cada uno con sus propias relaciones de transición. En tales casos, los disparadores a nivel de sentencia que estén presentes se ejecutarán una vez por cada creación de un conjunto de relaciones de transición, garantizando que los disparadores vean cada fila afectada en una relación de transición una y solo una vez.

Los disparadores a nivel de sentencia en una vista se ejecutan solo si la acción en la vista es manejada por un disparador INSTEAD OF a nivel de fila. Si la acción es manejada por una regla INSTEAD, entonces cualquier sentencia emitida por la regla se ejecuta en lugar de la sentencia original que nombra la vista, por lo que los disparadores que se ejecutarán serán aquellos en las tablas nombradas en las sentencias de reemplazo. Del mismo modo, si la vista es actualizable automáticamente, entonces la acción se maneja reescribiendo automáticamente la sentencia en una acción en la tabla base de la vista, por lo que los disparadores a nivel de sentencia de la tabla base son los que se ejecutan.

Modificar una tabla particionada o una tabla con hijos heredados ejecuta disparadores a nivel de sentencia asociados a la tabla nombrada explícitamente, pero no disparadores a nivel de sentencia para sus particiones o tablas hijas. En cambio, los disparadores a nivel de fila se ejecutan en las filas de las particiones o tablas hijas afectadas, incluso si no están nombradas explícitamente en la consulta. Si se ha definido un disparador a nivel de sentencia con relaciones de transición nombradas mediante una cláusula REFERENCING, entonces las imágenes previas y posteriores de las filas serán visibles desde todas las particiones o tablas hijas afectadas. En el caso de hijos heredados, las imágenes de las filas incluyen únicamente las columnas presentes en la tabla a la que está asociado el disparador.

Actualmente, los disparadores a nivel de fila con relaciones de transición no se pueden definir en particiones o tablas hijas de herencia. Además, los disparadores en tablas particionadas no pueden ser de tipo INSTEAD OF.

Actualmente, la opción OR REPLACE no es compatible con los disparadores de restricción.

No se recomienda reemplazar un disparador existente dentro de una transacción que ya ha realizado acciones de actualización en la tabla del disparador. Las decisiones de ejecución del disparador, o partes de las decisiones de ejecución que ya se hayan tomado, no se reconsiderarán, por lo que los efectos podrían ser sorprendentes.

Hay algunas funciones de disparador integradas que se pueden utilizar para resolver problemas comunes sin tener que escribir su propio código de disparador; consulta Section 9.29.

Ejemplos

Ejecuta la función check_account_update cada vez que una fila de la tabla accounts esté a punto de actualizarse:

CREATE TRIGGER check_update
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    EXECUTE FUNCTION check_account_update();

Modifica la definición de ese disparador para ejecutar la función únicamente si la columna balance se especifica como destino en el comando UPDATE:

CREATE OR REPLACE TRIGGER check_update
    BEFORE UPDATE OF balance ON accounts
    FOR EACH ROW
    EXECUTE FUNCTION check_account_update();

Esta forma solo ejecuta la función si la columna balance ha cambiado de valor:

CREATE TRIGGER check_update
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
    EXECUTE FUNCTION check_account_update();

Llama a una función para registrar las actualizaciones de accounts, pero solo si algo cambió:

CREATE TRIGGER log_update
    AFTER UPDATE ON accounts
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
    EXECUTE FUNCTION log_account_update();

Ejecuta la función view_insert_row para cada fila a fin de insertar filas en las tablas subyacentes de una vista:

CREATE TRIGGER view_insert
    INSTEAD OF INSERT ON my_view
    FOR EACH ROW
    EXECUTE FUNCTION view_insert_row();

Ejecuta la función check_transfer_balances_to_zero para cada sentencia a fin de confirmar que las filas de transfer se compensan para un neto de cero:

CREATE TRIGGER transfer_insert
    AFTER INSERT ON transfer
    REFERENCING NEW TABLE AS inserted
    FOR EACH STATEMENT
    EXECUTE FUNCTION check_transfer_balances_to_zero();

Ejecuta la función check_matching_pairs para cada fila a fin de confirmar que los cambios se realizan en pares coincidentes al mismo tiempo (por la misma sentencia):

CREATE TRIGGER paired_items_update
    AFTER UPDATE ON paired_items
    REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab
    FOR EACH ROW
    EXECUTE FUNCTION check_matching_pairs();

Section 37.4 contiene un ejemplo completo de una función de disparador escrita en C.

Compatibilidad

La sentencia CREATE TRIGGER en PostgreSQL implementa un subconjunto del estándar SQL. Actualmente faltan las siguientes funcionalidades:

SQL especifica que los disparadores múltiples deben ejecutarse en el orden del momento de su creación. PostgreSQL utiliza el orden alfabético de los nombres, lo que se consideró más conveniente.

SQL especifica que los disparadores BEFORE DELETE en las eliminaciones en cascada se ejecutan después de que se complete el DELETE en cascada. El comportamiento de PostgreSQL es que BEFORE DELETE siempre se ejecute antes de la acción de eliminación, incluso una en cascada. Esto se considera más coherente. También hay un comportamiento no estándar si los disparadores BEFORE modifican filas o evitan actualizaciones durante una actualización causada por una acción referencial. Esto puede provocar violaciones de restricciones o datos almacenados que no respeten la restricción referencial.

La capacidad de especificar múltiples acciones para un solo disparador utilizando OR es una extensión de PostgreSQL al estándar SQL.

La capacidad de ejecutar disparadores para TRUNCATE es una extensión de PostgreSQL al estándar SQL, al igual que la capacidad de definir disparadores a nivel de sentencia en vistas.

CREATE CONSTRAINT TRIGGER es una extensión de PostgreSQL al estándar SQL. También lo es la opción OR REPLACE.

Consulte también