Cómo crear disparadores PostgreSQL para el registro automatizado de bases de datos

¿Estás cansado de hacer un seguimiento manual de los cambios en las tablas de tu base de datos? ¿Te gustaría que hubiera una manera más fácil de registrar las actualizaciones y mantener un registro de todos los cambios que se han realizado? ¡Si es así, estás de suerte! En este tutorial, te mostrare cómo crear un disparador de PostgreSQL que registre automáticamente los cambios en una columna específica de tu tabla.

Digamos que tienes una tabla llamada leads, que contiene información sobre clientes potenciales. Quieres hacer un seguimiento de los cambios en la columna de estado, que indica si un lead ha sido asignado automáticamente a un representante de ventas o no. Cada vez que se actualice la columna de estado, quieres crear un nuevo registro en una tabla de registro de estado separada, que contenga el lead_id, la hora actual y el nuevo valor de estado.

Con un disparador de PostgreSQL, puedes automatizar este proceso y ahorrarte la molestia de hacer un seguimiento manual de los cambios. En este tutorial, te guiare a través de los pasos para crear una función de disparador en PostgreSQL y utilizarla para registrar los cambios en tu base de datos. Incluso si eres un desarrollador junior o eres nuevo en PostgreSQL, encontrarás este tutorial fácil de seguir y entender.

¡Así que empecemos y aprendamos a crear un disparador de PostgreSQL que registre automáticamente los cambios en tu base de datos!

Creamos función

Puedes crear una función de disparador (Trigger) en PostgreSQL que se ejecute cada vez que se actualice la columna de estado en la tabla de leads. Esta función insertará un nuevo registro en la tabla de registro de estado con el lead_id, la hora actual y el nuevo valor de estado.

Aquí tienes un ejemplo de cómo crear la función de disparador:

CREATE OR REPLACE FUNCTION log_lead_status_changes()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.status <> OLD.status THEN -- only log changes
    INSERT INTO status_log (lead_id, time, new_status)
      VALUES (NEW.id, now(), NEW.status);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

En este ejemplo, la función log_lead_status_changes verifica si la columna de estado ha cambiado comparando los valores NEW y OLD de la fila. Si el valor ha cambiado, inserta un nuevo registro en la tabla de registro de estado con el lead_id, la hora actual y el nuevo valor de estado. Por último, devuelve la fila NEW para indicar que la función de disparador se ha completado correctamente.

Creamos Trigger

Para crear el disparador que llama a esta función, puedes utilizar la siguiente declaración SQL:

CREATE TRIGGER lead_status_change
AFTER UPDATE OF status ON leads
FOR EACH ROW
EXECUTE FUNCTION log_lead_status_changes();

Esto crea un disparador llamado lead_status_change que se dispara DESPUÉS de una actualización de la columna de estado de la tabla leads, PARA CADA FILA de la tabla actualizada. El disparador llama a la función log_lead_status_changes que definiste anteriormente.

Con este disparador en su lugar, cada vez que se actualice la columna de estado en la tabla leads, se insertará un nuevo registro en la tabla de registro de estado con el lead_id, la hora actual y el nuevo valor de estado.

Ahora puedes escribir una cola simple en tu backend que verifique la tabla de registro de estado y envíe notificaciones de cambios. Y luego elimine el registro de la tabla.