PostgreSQL CREAR TRIGGER

Los desencadenantes en PostgreSQL son funciones de retrollamada de la base de datos que se ejecutan o disparan automáticamente cuando se produce un evento especificado en la base de datos, como una operación de `INSERT`, `UPDATE`, `DELETE` o incluso `TRUNCATE`. Se utilizan para aplicar reglas de negocio, validar datos y mantener la integridad de los datos dentro de la base de datos.

Utilización

Los desencadenantes se utilizan para automatizar tareas que deben producirse cuando ocurren eventos específicos de la base de datos, reduciendo la necesidad de intervención manual o de lógica del lado de la aplicación. Se definen mediante la sentencia `CREATE TRIGGER`, especificando el evento y la acción a realizar.

sql
CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF }
{ INSERT | UPDATE | DELETE | TRUNCATE }
ON table_name
[ FOR EACH ROW | FOR EACH STATEMENT ]
EXECUTE PROCEDURE function_name();

En esta sintaxis, `CREAR DISPARADOR` define un nuevo disparador llamado `nombre_disparador` que ejecuta una función especificada (`nombre_función`) antes, después o en lugar del evento especificado en `nombre_tabla`. La cláusula `POR CADA FILA` indica que el desencadenante se ejecuta una vez por cada fila afectada por el evento, mientras que `POR CADA DECLARACIÓN` se ejecuta una vez por cada sentencia desencadenante, independientemente del número de filas afectadas.

Ejemplos

1. Activador básico de inserción

sql
CREATE FUNCTION log_insert() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation) VALUES ('employees', 'INSERT');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_employee_inserts
AFTER INSERT ON employees
FOR EACH ROW EXECUTE PROCEDURE log_insert();

Este ejemplo crea un disparador que registra cada operación de inserción en la tabla "empleados" en una tabla "registro_auditoría".

2. Actualizar activador con condición

sql
CREATE FUNCTION check_salary_update() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.salary < OLD.salary THEN
    RAISE EXCEPTION 'Salary cannot be decreased';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_salary_decrease
BEFORE UPDATE ON employees
FOR EACH ROW EXECUTE PROCEDURE check_salary_update();

Este activador impide cualquier disminución del salario de los empleados, garantizando que las actualizaciones salariales sólo puedan aumentar o permanecer igual. Aquí, `NEW` y `OLD` representan los estados nuevo y antiguo de la fila sobre la que se opera, respectivamente.

3. Desencadenante de la eliminación en cascada

sql
CREATE FUNCTION delete_related_records() RETURNS TRIGGER AS $$
BEGIN
  DELETE FROM orders WHERE customer_id = OLD.customer_id;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER cascade_delete_orders
AFTER DELETE ON customers
FOR EACH ROW EXECUTE PROCEDURE delete_related_records();

Este ejemplo configura un desencadenante para eliminar automáticamente todos los pedidos asociados a un cliente cuando se elimina dicho cliente.

4. Ejemplo de activador en lugar de

Los activadores INSTEAD OF se suelen utilizar con vistas para realizar acciones en lugar de la operación por defecto.

sql
CREATE FUNCTION log_view_update() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation) VALUES ('view_name', 'UPDATE');
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_view_updates
INSTEAD OF UPDATE ON my_view
FOR EACH ROW EXECUTE PROCEDURE log_view_update();

Este activador registra los intentos de actualizar una vista en lugar de realizar la actualización.

Consejos y buenas prácticas

• Define claramente las condiciones de activación. Asegúrate de que las condiciones en las que se disparan los activadores están bien definidas para evitar comportamientos inesperados.
• Utiliza los desencadenantes con moderación. El uso excesivo de activadores puede dar lugar a interdependencias complejas y dificultar la depuración.
• Documenta minuciosamente los desencadenantes. Proporciona documentación clara sobre lo que hace cada activador y por qué se utiliza, para futuras referencias.
• Prueba a fondo. Prueba siempre los activadores en un entorno de desarrollo para asegurarte de que funcionan según lo previsto sin degradar el rendimiento.
• Considera las implicaciones para el rendimiento. Los activadores complejos pueden afectar al rendimiento; contrólalos y optimízalos cuando sea necesario.
• Evita la invocación recursiva. Utiliza comandos como `ALTER TABLE ... ACTIVAR DISPARO ...` para controlar la activación del disparador y evitar posibles bucles de invocación recursivos.