Ejemplo de CREATE TRIGGER de Postgresql

CREATE TRIGGER – define un nuevo disparador

Sinopsis

CREATE[CONSTRAINT]TRIGGER name  BEFORE   event [OR...] 
    ON table_name
    [FROM referenced_table_name ][NOT DEFERRABLE |[ DEFERRABLE ][ INITIALLY IMMEDIATE | INITIALLY DEFERRED ]][ REFERENCING   NEW  TABLE[AS] transition_relation_name  [...]][FOR[ EACH ]  STATEMENT  ][WHEN( condition )]EXECUTE PROCEDURE  function_name ( arguments )where event can be one of:

    INSERTUPDATE[OF column_name [,...]]DELETETRUNCATE

Descripción

CREATE TRIGGER crea un nuevo disparador. El disparador se asociará con la tabla, vista o tabla externa especificada y ejecutará la función especificada function_name cuando se realizan determinadas operaciones en esa mesa.

Se puede especificar que el disparador se dispare antes de que se intente la operación en una fila (antes de que se verifiquen las restricciones y INSERT, UPDATE, o DELETE se intenta); o después de que la operación se haya completado (después de que se verifiquen las restricciones y INSERT, UPDATE, o DELETE ha completado); o en lugar de la operación (en el caso de inserciones, actualizaciones o eliminaciones en una vista). Si el disparador se dispara antes o en lugar del evento, el disparador puede omitir la operación de la fila actual o cambiar la fila que se está insertando (por INSERT y UPDATE operaciones solamente). Si el disparador se dispara después del evento, todos los cambios, incluidos los efectos de otros disparadores, son “visible“ al gatillo.

Un gatillo que está marcado FOR EACH ROW se llama una vez por cada fila que modifica la operación. Por ejemplo, un DELETE que afecta a 10 filas provocará cualquier ON DELETE desencadena en la relación de destino que se llamará 10 veces por separado, una por cada fila eliminada. Por el contrario, un disparador que está marcado 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 modifica cero filas aún resultará en la ejecución de cualquier FOR EACH STATEMENT desencadenantes).

Disparadores que están especificados para disparar INSTEAD OF el evento desencadenante debe estar marcado FOR EACH ROWy solo se puede definir en vistas. BEFORE y AFTER los disparadores en una vista deben estar marcados como FOR EACH STATEMENT.

Además, se pueden definir desencadenantes para disparar TRUNCATEaunque solo FOR EACH STATEMENT.

La siguiente tabla resume los tipos de disparadores que se pueden usar en tablas, vistas y tablas externas:

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

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

Cuando el CONSTRAINT se especifica la opción, este comando crea una disparador de restricción. Esto es lo mismo que un gatillo normal, excepto que la sincronización del disparo del gatillo se puede ajustar usando SET CONSTRAINTS. Los desencadenantes de restricciones deben ser AFTER ROW disparadores en tablas simples (no tablas externas). Se pueden disparar al final de la declaración que causa el evento desencadenante o al final de la transacción que los contiene; en el último caso se dice que son diferido. También se puede forzar un disparo de gatillo diferido pendiente para que suceda de inmediato mediante el uso de SET CONSTRAINTS. Se espera que los activadores de restricciones generen una excepción cuando se violen las restricciones que implementan.

los REFERENCING La opción permite la recopilación de relaciones de transición, que son conjuntos de filas que incluyen todas las filas insertadas, eliminadas o modificadas por la instrucción SQL actual. Esta función permite al disparador ver una vista global de lo que hizo la declaración, no solo una fila a la vez. Esta opción solo está permitida para AFTER disparador que no es un disparador de restricción; también, si el disparador es un UPDATE disparador, no debe especificar un column_name lista. OLD TABLE solo se puede especificar una vez, y solo para un disparador que puede disparar en UPDATE o DELETE; crea una relación de transición que contiene el imagenes-de-antes de todas las filas actualizadas o eliminadas por la declaración. Similar, NEW TABLE solo se puede especificar una vez, y solo para un disparador que puede disparar en UPDATE o INSERT; crea una relación de transición que contiene el imágenes posteriores de todas las filas actualizadas o insertadas por la declaración.

SELECT no modifica ninguna fila, por lo que no puede crear SELECT desencadenantes. Las reglas y puntos de vista pueden proporcionar soluciones viables a problemas que parecen necesitar SELECT desencadenantes.

Referirse a Capítulo 38 para obtener más información sobre los desencadenantes.

Parámetros

name

El nombre que se le dará al nuevo disparador. Debe ser distinto del nombre de cualquier otro activador de la misma tabla. El nombre no puede estar calificado por esquema: el desencadenador hereda el esquema de su tabla. Para un disparador de restricción, este es también el nombre que se debe usar al modificar el comportamiento del disparador usando SET CONSTRAINTS.

BEFOREAFTERINSTEAD OF

Determina si la función se llama antes, después o en lugar del evento. Un disparador de restricción solo se puede especificar como AFTER.

event

Uno de INSERT, UPDATE, DELETE, o TRUNCATE; esto especifica el evento que disparará el gatillo. Se pueden especificar varios eventos usando OR, excepto cuando se soliciten relaciones de transición.

Para UPDATE eventos, es posible especificar una lista de columnas usando esta sintaxis:

UPDATEOF column_name1 [, column_name2 ...]

El disparador solo se activará si al menos una de las columnas enumeradas se menciona como objetivo del UPDATE comando o si una de las columnas enumeradas es una columna generada que depende de una columna que es el destino del UPDATE.

INSTEAD OF UPDATE los eventos no permiten una lista de columnas. Tampoco se puede especificar una lista de columnas al solicitar relaciones de transición.

table_name

El nombre (opcionalmente calificado por esquema) de la tabla, vista o tabla externa para la que es el desencadenante.

referenced_table_name

El nombre (posiblemente calificado por esquema) de otra tabla a la que hace referencia la restricción. Esta opción se usa para restricciones de clave externa y no se recomienda para uso general. Esto solo se puede especificar para activadores de restricciones.

DEFERRABLENOT DEFERRABLEINITIALLY IMMEDIATEINITIALLY DEFERRED

El tiempo predeterminado del disparador. Consulte la documentación de CREATE TABLE para obtener detalles de estas opciones de restricción. Esto solo se puede especificar para activadores de restricciones.

REFERENCING

Esta palabra clave precede inmediatamente a la declaración de uno o dos nombres de relación que proporcionan acceso a las relaciones de transición de la declaración de activación.

OLD TABLENEW TABLE

Esta cláusula indica si el siguiente nombre de relación es para la relación de transición de la imagen anterior o la relación de transición de la imagen posterior.

transition_relation_name

El nombre (no calificado) que se utilizará en el desencadenante de esta relación de transición.

FOR EACH ROWFOR EACH STATEMENT

Esto especifica si la función de activación debe activarse una vez por cada fila afectada por el evento de activación o solo una vez por instrucción SQL. Si no se especifica ninguno, FOR EACH STATEMENT es el predeterminado. Los activadores de restricciones solo se pueden especificar FOR EACH ROW.

condition

Una expresión booleana que determina si la función de activación se ejecutará realmente. Si WHEN se especifica, la función sólo se llamará si el condition devoluciones true. En FOR EACH ROW desencadenantes, el WHEN La condición puede referirse a columnas de los valores de fila antiguos y / o nuevos escribiendo OLD.column_name o NEW.column_name respectivamente. Por supuesto, INSERT los desencadenantes no pueden referirse a OLD y DELETE los desencadenantes no pueden referirse a NEW.

INSTEAD OF los desencadenantes no son compatibles WHEN condiciones.

En la actualidad, WHEN las expresiones no pueden contener subconsultas.

Tenga en cuenta que para los desencadenantes de restricciones, la evaluación de la WHEN La condición no se aplaza, pero ocurre inmediatamente después de que se realiza la operación de actualización de filas. Si la condición no se evalúa como verdadera, entonces el disparador no se pone en cola para ejecución diferida.

function_name

Una función proporcionada por el usuario que se declara que no toma argumentos y devuelve el tipo trigger, que se ejecuta cuando se dispara el disparador.

En la sintaxis de CREATE TRIGGER, las palabras clave FUNCTION y PROCEDURE son equivalentes, pero la función referenciada debe ser en cualquier caso una función, no un procedimiento. El uso de la palabra clave PROCEDURE aquí es histórico y obsoleto.

arguments

Una lista opcional de argumentos separados por comas que se proporcionará a la función cuando se ejecute el disparador. Los argumentos son constantes de cadena literal. Aquí también se pueden escribir nombres simples y constantes numéricas, pero todos se convertirán en cadenas. Consulte la descripción del lenguaje de implementación de la función de activación para averiguar cómo se puede acceder a estos argumentos dentro de la función; puede ser diferente de los argumentos de funciones normales.

Notas

Para crear un disparador en una mesa, el usuario debe tener el TRIGGER privilegio sobre la mesa. El usuario también debe tener EXECUTE privilegio sobre la función de disparo.

Utilice DROP TRIGGER para quitar un gatillo.

Un disparador específico de columna (uno definido mediante el UPDATE OF column_name sintaxis) se activará cuando cualquiera de sus columnas se enumere como destinos en el UPDATE comando SET lista. Es posible que el valor de una columna cambie incluso cuando el disparador no se activa, porque los cambios realizados en el contenido de la fila por BEFORE UPDATE no se consideran los desencadenantes. Por el contrario, un comando como UPDATE ... SET x = x ... disparará un gatillo en la columna x, aunque el valor de la columna no cambió.

Hay algunas funciones de activación integradas que se pueden utilizar para resolver problemas comunes sin tener que escribir su propio código de activación; ver Sección 9.28.

en un BEFORE gatillo, el WHEN La condición se evalúa justo antes de que la función se ejecute o se ejecute, por lo que el uso WHEN no es materialmente diferente de probar la misma condición al comienzo de la función de activación. Tenga en cuenta en particular que el NEW La fila vista por la condición es el valor actual, posiblemente modificado por desencadenadores anteriores. También una BEFORE gatillo WHEN la condición no es Permitido examinar las columnas del sistema de la NEW fila (como ctid), porque aún no se habrán configurado.

En un AFTER gatillo, el WHEN La condición se evalúa justo después de que se produce la actualización de la fila y determina si un evento está en cola para disparar el disparador al final de la instrucción. Entonces cuando un AFTER gatillo WHEN condición no devuelve verdadero, no es necesario poner en cola un evento ni volver a buscar la fila al final de la declaración. Esto puede resultar en una aceleración significativa en las declaraciones que modifican muchas filas, si el disparador solo necesita activarse para algunas de las filas.

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

Actualizaciones o eliminaciones de filas provocadas por acciones de aplicación de claves externas, como ON UPDATE CASCADE o ON DELETE SET NULL, se tratan como parte del comando SQL que los causó (tenga en cuenta que tales acciones nunca se aplazan). Se dispararán los disparadores relevantes en la tabla afectada, por lo que esto proporciona otra forma en la que un comando SQL puede disparar disparadores que no coinciden directamente con su tipo. En casos simples, 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 sola relación de transición. Sin embargo, hay casos en los que la presencia de un AFTER ROW El activador que solicita relaciones de transición hará que las acciones de aplicación de clave externa activadas por un solo comando SQL se dividan en varios pasos, cada uno con su (s) propia (s) relación (es) de transición. En tales casos, cualquier activador de nivel de instrucción que esté presente se activará una vez por creación de un conjunto de relaciones de transición, lo que garantiza que los activadores vean cada fila afectada en una relación de transición una vez y solo una vez.

Los desencadenadores de nivel de instrucción en una vista se activan solo si la acción en la vista es manejada por un nivel de fila INSTEAD OF desencadenar. Si la acción es manejada por un INSTEAD regla, todas las declaraciones emitidas por la regla se ejecutan en lugar de la declaración original que nombra la vista, de modo que los desencadenantes que se dispararán son los de las tablas nombradas en las declaraciones de reemplazo. De manera similar, si la vista se puede actualizar automáticamente, entonces la acción se maneja reescribiendo automáticamente la instrucción en una acción en la tabla base de la vista, de modo que los desencadenadores de nivel de instrucción de la tabla base sean los que se disparen.

La creación de un disparador a nivel de fila en una tabla particionada hará que se creen disparadores idénticos en todas sus particiones existentes; y todas las particiones creadas o adjuntas posteriormente también contendrán un disparador idéntico. Si la partición está separada de su padre, el disparador se elimina. Los disparadores en tablas particionadas pueden no ser INSTEAD OF.

La modificación de una tabla particionada o una tabla con herencia secundaria desencadena desencadenadores de nivel de instrucción adjuntos a la tabla nombrada explícitamente, pero no desencadenadores de nivel de instrucción para sus particiones o tablas secundarias. Por el contrario, los desencadenadores de nivel de fila se activan en las filas de las particiones afectadas o las tablas secundarias, incluso si no se nombran explícitamente en la consulta. Si se ha definido un desencadenante a nivel de declaración con relaciones de transición nombradas por un REFERENCING cláusula, luego las imágenes de antes y después de las filas son visibles desde todas las particiones afectadas o tablas secundarias. En el caso de los hijos de herencia, las imágenes de fila incluyen solo las columnas que están presentes en la tabla a la que está asociado el desencadenador. Actualmente, los desencadenadores de nivel de fila con relaciones de transición no se pueden definir en particiones o tablas secundarias de herencia.

Ejemplos de

Ejecuta la función check_account_update siempre que una fila de la mesa accounts está a punto de actualizarse:

CREATETRIGGER check_update
    BEFORE UPDATEON accounts
    FOR EACH ROWEXECUTEFUNCTION check_account_update();

Lo mismo, pero solo ejecuta la función si la columna balance se especifica como un objetivo en el UPDATE mando:

CREATETRIGGER check_update
    BEFORE UPDATEOF balance ON accounts
    FOR EACH ROWEXECUTEFUNCTION check_account_update();

Este formulario solo ejecuta la función si la columna balance de hecho ha cambiado de valor:

CREATETRIGGER check_update
    BEFORE UPDATEON accounts
    FOR EACH ROWWHEN(OLD.balance ISDISTINCTFROM NEW.balance)EXECUTEFUNCTION check_account_update();

Llame a una función para registrar actualizaciones de accounts, pero solo si algo cambia:

CREATETRIGGER log_update
    AFTERUPDATEON accounts
    FOR EACH ROWWHEN(OLD.*ISDISTINCTFROM NEW.*)EXECUTEFUNCTION log_account_update();

Ejecuta la función view_insert_row para que cada fila inserte filas en las tablas subyacentes a una vista:

CREATETRIGGER view_insert
    INSTEAD OFINSERTON my_view
    FOR EACH ROWEXECUTEFUNCTION view_insert_row();

Ejecuta la función check_transfer_balances_to_zero para cada declaración para confirmar que el transfer filas desplazadas a un neto de cero:

CREATETRIGGER transfer_insert
    AFTERINSERTON transfer
    REFERENCING NEW TABLEAS inserted
    FOR EACH STATEMENT
    EXECUTEFUNCTION check_transfer_balances_to_zero();

Ejecuta la función check_matching_pairs para cada fila para confirmar que se realizan cambios en los pares coincidentes al mismo tiempo (mediante la misma declaración):

CREATETRIGGER paired_items_update
    AFTERUPDATEON paired_items
    REFERENCING NEW TABLEAS newtab OLD TABLEAS oldtab
    FOR EACH ROWEXECUTEFUNCTION check_matching_pairs();

Sección 38.4 contiene un ejemplo completo de una función de activación escrita en C.

Compatibilidad

los CREATE TRIGGER declaración en PostgreSQL implementa un subconjunto del estándar SQL. Actualmente faltan las siguientes funcionalidades:

• Mientras que los nombres de las tablas de transición para AFTER los desencadenantes se especifican mediante el REFERENCING cláusula de la forma estándar, las variables de fila utilizadas en FOR EACH ROW Es posible que los desencadenantes no se especifiquen en un REFERENCING cláusula. Están disponibles de una manera que depende del idioma en el que está escrita la función de activación, pero están fijos para cualquier idioma. Algunos lenguajes se comportan efectivamente como si hubiera una REFERENCING cláusula que contiene OLD ROW AS OLD NEW ROW AS NEW.

• El estándar permite que las tablas de transición se utilicen con columnas específicas. UPDATE disparadores, pero luego el conjunto de filas que deberían estar visibles en las tablas de transición depende de la lista de columnas del disparador. Actualmente, PostgreSQL no implementa esto.

• PostgreSQL solo permite la ejecución de una función definida por el usuario para la acción desencadenada. El estándar permite la ejecución de varios otros comandos SQL, como CREATE TABLE, como la acción desencadenada. No es difícil evitar esta limitación creando una función definida por el usuario que ejecute los comandos deseados.

SQL especifica que se deben activar varios activadores en el orden de creación. PostgreSQL usa el orden de los nombres, que se consideró más conveniente.

SQL especifica que BEFORE DELETE dispara en cascada elimina el fuego después la cascada DELETE completa. El comportamiento de PostgreSQL es para BEFORE DELETE para disparar siempre antes de la acción de eliminación, incluso una en cascada. Esto se considera más consistente. También hay un comportamiento no estándar si BEFORE Los desencadenadores modifican filas o evitan actualizaciones durante una actualización provocada por una acción referencial. Esto puede dar lugar a violaciones de restricciones o datos almacenados que no respeten la restricción de referencia.

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

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

CREATE CONSTRAINT TRIGGER es una extensión PostgreSQL del estándar SQL.

Ver también

ALTER TRIGGER, SOLTAR GATILLO, CREAR FUNCIÓN, ESTABLECER RESTRICCIONES