Funciones, procedimientos y triggers en SQL

1. Funciones (Functions)

Una función es un objeto de la base de datos que encapsula una operación reutilizable. Puede recibir parámetros, ejecutar instrucciones y devolver un resultado.

Las funciones son útiles para centralizar lógica que debe ejecutarse desde diferentes consultas u operaciones.

Ejemplo en PostgreSQL

CREATE OR REPLACE FUNCTION sumar(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a + b;
END;
$$;

Para ejecutarla:

SELECT sumar(5, 3);

Resultado: 8.

Elementos importantes

• CREATE OR REPLACE FUNCTION: crea la función o reemplaza su definición.
• RETURNS INTEGER: declara el tipo de dato que devuelve.
• LANGUAGE plpgsql: indica que se utiliza PL/pgSQL.
• BEGIN ... END: delimita el bloque de instrucciones.
• RETURN: devuelve un resultado.

PostgreSQL también permite funciones escritas en SQL, por ejemplo:

CREATE OR REPLACE FUNCTION obtener_total()
RETURNS INTEGER
LANGUAGE SQL
AS $$
    SELECT COUNT(*)::INTEGER
    FROM categoria;
$$;

Equivalencias en otros gestores

• MySQL: CREATE FUNCTION, con una definición y un RETURN.
• SQL Server: CREATE FUNCTION, normalmente con RETURNS y una expresión o bloque de instrucciones, según el tipo de función.

La sintaxis exacta y las restricciones varían según el gestor.

---

2. Procedimientos almacenados (Stored Procedures)

Un procedimiento almacenado es un objeto que agrupa instrucciones para ejecutar una operación o una secuencia de operaciones.

Puede servir para procesos de negocio, mantenimiento de datos o tareas administrativas.

Ejemplo en PostgreSQL

CREATE OR REPLACE PROCEDURE desactivar_categoria(
    p_id BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE categoria
    SET activo = FALSE
    WHERE id_categoria = p_id;
END;
$$;

Para ejecutarlo:

CALL desactivar_categoria(3);

Este procedimiento desactiva la categoría cuyo identificador es 3, si existe.

Diferencia entre función y procedimiento

Característica	Función	Procedimiento
Propósito habitual	Calcular o devolver un resultado	Ejecutar una operación o proceso
Ejecución en PostgreSQL	SELECT o dentro de una expresión compatible	CALL
RETURNS	Declara un resultado o tipo de retorno	No utiliza la misma declaración de retorno que una función
Uso en consultas	Puede utilizarse en expresiones compatibles	No se utiliza como una expresión de consulta
Transacciones	Su comportamiento está sujeto a las restricciones de las funciones	PostgreSQL permite control transaccional en ciertos procedimientos y contextos

Regla mental: una función devuelve un resultado que puedes utilizar; un procedimiento ejecuta una operación que invocas explícitamente. No es una separación absoluta de responsabilidades, porque depende del gestor y del diseño.

En MySQL y SQL Server también existen procedimientos almacenados, pero sus reglas de ejecución y manejo de transacciones difieren.

---

3. Triggers (disparadores)

Un trigger es un mecanismo que ejecuta una función o bloque de instrucciones automáticamente cuando ocurre un evento definido sobre un objeto de la base de datos.

Los eventos habituales incluyen:

• INSERT: se inserta un registro.
• UPDATE: se modifica un registro.
• DELETE: se elimina un registro.

Según el gestor, también pueden existir triggers para eventos DDL, como la creación o modificación de objetos.

¿Para qué sirven?

• Mantener fechas de modificación.
• Registrar auditorías.
• Validar o complementar operaciones.
• Mantener información derivada.
• Ejecutar lógica relacionada con cambios en los datos.

Ejemplo en PostgreSQL: actualizar una fecha de modificación

Supongamos que una tabla tiene la columna:

fecha_modificacion TIMESTAMP
    NOT NULL DEFAULT CURRENT_TIMESTAMP

El DEFAULT establece el valor inicial al insertar una fila, pero no actualiza automáticamente la fecha cuando se modifica esa fila.

Para hacerlo, podemos utilizar una función y un trigger.

Paso 1. Crear la función

CREATE OR REPLACE FUNCTION actualizar_fecha_modificacion()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.fecha_modificacion := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$;

Elementos importantes:

• RETURNS TRIGGER: indica que es una función destinada a un trigger.
• NEW: representa la nueva versión de la fila en triggers de inserción o actualización.
• OLD: representa la versión anterior de la fila en operaciones donde está disponible, como UPDATE y DELETE.
• RETURN NEW: permite continuar con la nueva fila modificada en un trigger de fila BEFORE que admite ese retorno.

Paso 2. Crear el trigger

CREATE TRIGGER trg_actualizar_fecha_modificacion
BEFORE UPDATE ON categoria
FOR EACH ROW
EXECUTE FUNCTION actualizar_fecha_modificacion();

¿Qué significa cada parte?

• BEFORE UPDATE: se ejecuta antes de actualizar una fila.
• ON categoria: el trigger pertenece a esa tabla.
• FOR EACH ROW: se ejecuta una vez por cada fila afectada.
• EXECUTE FUNCTION: indica la función que PostgreSQL debe ejecutar.

Si se modifica una categoría, la función asigna la fecha y hora actuales a fecha_modificacion.

Importante: este trigger se ejecuta en cada actualización de una fila, aunque solo cambie activo. Si se desea limitarlo a determinados cambios, se pueden considerar triggers definidos con UPDATE OF columna o condiciones adicionales.

En PostgreSQL, el trigger y la función son objetos diferentes: crear la función no crea automáticamente el trigger.

---

4. Tipos de triggers y diferencias entre gestores

PostgreSQL

Los triggers de tabla pueden ejecutarse:

• BEFORE: antes de la operación.
• AFTER: después de la operación.
• INSTEAD OF: en vistas compatibles, para sustituir la operación original.

También existen triggers a nivel de fila (FOR EACH ROW) y de sentencia (FOR EACH STATEMENT).

MySQL

MySQL admite triggers BEFORE y AFTER para INSERT, UPDATE y DELETE.

Un trigger se define con CREATE TRIGGER y contiene sus propias instrucciones. MySQL no utiliza exactamente el mismo modelo de función disparadora que PostgreSQL.

Ejemplo:

DELIMITER //

CREATE TRIGGER trg_categoria_bu
BEFORE UPDATE ON categoria
FOR EACH ROW
BEGIN
    SET NEW.fecha_modificacion = CURRENT_TIMESTAMP;
END//

DELIMITER ;

DELIMITER es una instrucción del cliente MySQL, utilizada en herramientas como el cliente de consola para poder definir el bloque sin que el primer punto y coma lo termine prematuramente. No forma parte de la definición SQL que el servidor almacena.

SQL Server

SQL Server utiliza triggers como objetos que reaccionan a eventos. Los triggers DML suelen ejecutarse después de la operación, y también existen triggers INSTEAD OF.

En SQL Server, los triggers trabajan con tablas lógicas como inserted y deleted, que pueden contener varias filas. No se debe asumir que el trigger se ejecuta una vez por cada fila.

Conclusión: no copies directamente un trigger de un gestor a otro. Aunque los conceptos sean similares, su sintaxis y modelo de ejecución pueden ser diferentes.

---

5. Variables, condicionales y ciclos en PL/pgSQL

PL/pgSQL permite escribir lógica procedural dentro de funciones, procedimientos y triggers.

Variables

DECLARE
    total INTEGER;

DECLARE permite declarar variables locales en el bloque correspondiente.

Condicionales

IF total > 0 THEN
    RAISE NOTICE 'Hay registros';
ELSE
    RAISE NOTICE 'No hay registros';
END IF;

IF, THEN, ELSE y END IF permiten ejecutar instrucciones según una condición.

Ciclos

FOR i IN 1..5 LOOP
    RAISE NOTICE 'Número: %', i;
END LOOP;

FOR ... LOOP permite repetir instrucciones. También existen WHILE y otras formas de iteración.

Estas estructuras se parecen a las de los lenguajes de programación tradicionales, aunque pertenecen a PL/pgSQL y tienen sus propias reglas.

---

6. Manejo de excepciones

Las funciones y procedimientos pueden manejar errores mediante bloques EXCEPTION.

Ejemplo en PostgreSQL:

DO $$
BEGIN
    RAISE NOTICE 'Inicio del proceso';

    -- Instrucciones del proceso.

EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Ocurrió un error';
        RAISE;
END;
$$;

Este ejemplo utiliza un bloque anónimo DO para ilustrar la estructura. RAISE; vuelve a lanzar la excepción para que el error no quede oculto.

En código real, es preferible manejar explícitamente los errores que se espera que puedan ocurrir y no capturar indiscriminadamente todos los errores si no se van a resolver.

---

7. Vistas (Views)

Una vista es una consulta guardada que se expone como un objeto consultable. Normalmente no almacena por sí misma una copia independiente de los datos.

Ejemplo:

CREATE VIEW categorias_activas AS
SELECT id_categoria, nombre
FROM categoria
WHERE activo = TRUE;

Para consultarla:

SELECT *
FROM categorias_activas;

Las vistas ayudan a simplificar consultas y a controlar qué columnas o filas se exponen. No sustituyen por sí solas un sistema completo de permisos.

---

8. Índices (Indexes)

Un índice es una estructura auxiliar que puede acelerar determinadas consultas, a cambio de utilizar almacenamiento adicional y añadir trabajo durante algunas operaciones de escritura.

Ejemplo:

CREATE INDEX idx_categoria_activo
ON categoria (activo);

Este índice puede ayudar con consultas que filtran por activo, dependiendo de la distribución de los datos y del planificador.

Si la columna ya tiene una restricción UNIQUE o PRIMARY KEY, PostgreSQL normalmente crea el índice correspondiente automáticamente. No conviene duplicarlo sin una razón concreta.

---

9. Restricciones (Constraints)

Las restricciones mantienen la integridad de los datos.

Restricción	Función
PRIMARY KEY	Identifica de manera única cada fila y no admite NULL.
FOREIGN KEY	Mantiene la integridad referencial entre tablas.
UNIQUE	Impide duplicados en los valores o combinaciones cubiertas.
NOT NULL	Impide que una columna tenga valor NULL.
CHECK	Exige que se cumpla una condición.

Ejemplo:

CREATE TABLE producto (
    id_producto BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    precio NUMERIC(12, 2) NOT NULL CHECK (precio >= 0)
);

En general, utiliza restricciones declarativas para reglas de integridad que puedan expresarse con ellas, en vez de depender exclusivamente de triggers.

---

10. ¿Cuándo usar cada mecanismo?

Necesidad	Mecanismo que conviene evaluar
Calcular y devolver un resultado	Función
Ejecutar un proceso explícito	Procedimiento almacenado
Reaccionar automáticamente a un cambio	Trigger
Simplificar una consulta recurrente	Vista
Acelerar una búsqueda	Índice
Impedir datos inválidos o relaciones rotas	Restricción
Controlar quién puede realizar una operación	Roles y permisos

No toda lógica de negocio debe ir dentro de la base de datos. Algunas reglas conviene implementarlas en la aplicación; otras, especialmente las relacionadas con integridad de los datos, pueden beneficiarse de estar protegidas por la propia base de datos.

---

Resumen para recordar

• Función: recibe datos, ejecuta lógica y devuelve un resultado.
• Procedimiento: ejecuta un proceso invocado explícitamente.
• Trigger: reacciona automáticamente a un evento.
• NEW: nueva versión de la fila en triggers compatibles.
• OLD: versión anterior de la fila en triggers compatibles.
• BEFORE / AFTER: determinan cuándo se ejecuta el trigger con respecto a la operación.
• Vista: consulta guardada que se expone como objeto.
• Índice: estructura auxiliar para acelerar ciertas consultas.
• Constraint: regla declarativa que protege la integridad de los datos.
• PL/pgSQL: lenguaje procedural de PostgreSQL para escribir lógica de base de datos.

Documentación oficial

• PostgreSQL: PL/pgSQL
• PostgreSQL: CREATE FUNCTION
• PostgreSQL: CREATE PROCEDURE
• PostgreSQL: Triggers
• MySQL: Stored Objects
• SQL Server: CREATE TRIGGER