Disparadores de PostgreSQL

Los disparadores de PostgreSQL son funciones de devolución de llamada de la base de datos que se ejecutan/llaman automáticamente cuando ocurre el evento de base de datos especificado.

A continuación se presentan algunos puntos importantes sobre los disparadores de PostgreSQL:

Los disparadores de PostgreSQL se pueden activar en las siguientes situaciones:
- Antes de realizar la operación (antes de verificar las restricciones e intentar insertar, actualizar o eliminar).
- Después de realizar la operación (después de verificar las restricciones y completar la inserción, actualización o eliminación).
- Operaciones de actualización (al insertar, actualizar o eliminar en una vista).
La propiedad FOR EACH ROW del disparador es opcional; si se selecciona, se llama una vez por cada fila modificada por la operación; por el contrario, si se selecciona FOR EACH STATEMENT, el disparador se ejecuta una vez por cada sentencia, sin importar cuántas filas se modifiquen.
La cláusula WHEN y las operaciones del disparador pueden acceder a los elementos de cada fila al hacer referencia a NEW.column-name y OLD.column-name al insertar, eliminar o actualizar. Donde column-name es el nombre de la columna en la tabla asociada con el disparador.
Si existe una cláusula WHEN, la declaración de PostgreSQL solo se ejecuta para la fila en la que se cumple la cláusula WHEN; si no hay cláusula WHEN, la declaración de PostgreSQL se ejecuta en cada fila.
Las palabras clave BEFORE o AFTER determinan cuándo se ejecuta la acción del disparador, es decir, si se ejecuta antes o después de la inserción, modificación o eliminación de la fila asociada.
La tabla que se va a modificar debe existir en la misma base de datos que la tabla o vista a la que está adjunto el disparador, y solo debe usar tablename, no database.tablename.
Al crear un disparador de restricción, se especifican las opciones de restricción. Esto es igual que un disparador normal, solo que se puede usar esta restricción para ajustar el momento en que se activa el disparador. Cuando se viola la restricción implementada por el disparador de restricción, se lanzará una excepción.

Sintaxis

La sintaxis básica para crear un disparador es la siguiente:

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- 触发器逻辑....
];

Aquí, event_name puede ser una operación de base de datos INSERT, DELETE o UPDATE en la tabla table_name mencionada. Puede especificar opcionalmente FOR EACH ROW después del nombre de la tabla.

La siguiente es la sintaxis para crear un disparador en una o más columnas específicas de una tabla en una operación UPDATE:

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
 -- 触发器逻辑....
];

Ejemplo

Supongamos una situación en la que queremos mantener una auditoría para cada registro insertado en la tabla COMPANY recién creada (si ya existe, se elimina y se vuelve a crear):

ejemplodb=# CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Para mantener la auditoría, crearemos una nueva tabla llamada AUDIT. Cada vez que haya un nuevo registro en la tabla COMPANY, se insertará un mensaje de registro en ella:

ejemplodb=# CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

Aquí, ID es el ID del registro AUDIT, EMP_ID es el ID de la tabla COMPANY, y DATE mantendrá la marca de tiempo de cuando se creó el registro en COMPANY. Así que ahora creemos un disparador en la tabla COMPANY, como se muestra a continuación:

ejemplodb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

auditlogfunc() es una función de PostgreSQL, cuya definición es la siguiente:

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
   BEGIN
      INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$example_table$ LANGUAGE plpgsql;

Ahora, comencemos a insertar datos en la tabla COMPANY:

ejemplodb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );

En este momento, se insertó un registro en la tabla COMPANY:

Al mismo tiempo, también se insertó un registro en la tabla AUDIT, porque creamos un disparador al insertar en la tabla COMPANY. De manera similar, también podemos crear disparadores para actualizaciones y eliminaciones según sea necesario:

emp_id |          entry_date
--------+-------------------------------
      1 | 2013-05-05 15:49:59.968+05:30
(1 row)

Listar disparadores

Puedes listar todos los disparadores de la base de datos actual desde la tabla pg_trigger:

ejemplodb=# SELECT * FROM pg_trigger;

Si deseas listar los disparadores de una tabla específica, la sintaxis es la siguiente:

ejemplodb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';

El resultado es el siguiente:

 tgname
-----------------
 example_trigger
(1 row)

Eliminar disparadores

La sintaxis básica para eliminar un disparador es la siguiente:

drop trigger ${trigger_name} on ${table_of_trigger_dependent};

El comando para eliminar el disparador example_trigger en la tabla company de este artículo es:

drop trigger example_trigger on company;

Otras extensiones

PostgreSQL Avanzado

Disparadores de PostgreSQL

Sintaxis

Ejemplo

Listar disparadores

Eliminar disparadores