Tema 3: SQL. Procedimientos almacenados y disparadores

Consultas SQL (DML)

SQL (Structured Query Language) es el lenguaje de interrogación y manipulación de bases de datos relacionales. Es el estándar de facto cuando hablamos de lenguajes de interrogación de bases de datos y su base es el álgebra relacional. Fue normalizado por ANSI/ISO (ISO/IEC 9075), con hitos como SQL-86, SQL-89 y SQL-92 (SQL2), y es hoy el lenguaje de manipulación de datos más utilizado en la actualidad.

Además de SQL existen otros lenguajes de manipulación de datos (DML, Data Manipulation Language) históricos de modelos pre-relacionales: IMS/DL1 (modelo jerárquico de IBM, Data Language One) y CODASYL (modelo en red/DBTG). El objetivo del DML es consultar y modificar los datos de la base de datos. En SQL, los comandos DML son SELECT, INSERT, UPDATE y DELETE (p. ej., INSERT es un comando DML); no debe confundirse con DDL (CREATE, ALTER, DROP) ni DCL (GRANT, REVOKE).

La sentencia SELECT y el orden de las cláusulas

Una consulta se estructura con cláusulas que deben escribirse en un orden fijo:

SELECT columnas

FROM tabla

WHERE condición

GROUP BY columnas

HAVING condición_de_grupo

ORDER BY columnas ASC|DESC

La cláusula ORDER BY va después de WHERE, GROUP BY y HAVING, y ordena los resultados en función de una o más columnas: su formato es ORDER BY nombrecolumna ASC|DESC (ascendente por defecto). Si se indican varias columnas, se ordena por la primera y, dentro de sus grupos, por la siguiente; el modificador ASC|DESC aplica a cada columna por separado. Así, ORDER BY Provincia DESC, Municipio ordena descendente por Provincia y, dentro de cada provincia, ascendente por Municipio (ASC es el valor por defecto).

La cláusula LIMIT (MySQL/PostgreSQL) limita el número de filas mostradas en el resultado.

WHERE frente a HAVING

Ambas eliminan datos no deseados del informe, pero actúan en momentos distintos del procesamiento:

• WHERE filtra filas individuales antes de la agrupación (se usa con la selección de columnas y no admite funciones de agregación).

• HAVING filtra grupos después de aplicar GROUP BY (se usa con funciones de agregación). La cláusula HAVING solo se puede utilizar con datos agrupados y se emplea habitualmente en combinación con GROUP BY.

GROUP BY y funciones de agregación

GROUP BY agrupa varias filas en una sola por el valor de una o más columnas. Las funciones de agregación del estándar son COUNT, MAX, MIN, AVG y SUM (GROUP BY no es una función de agregación, es una cláusula). Ejemplos habituales:

• SELECT departamento, COUNT(*) FROM empleados GROUP BY departamento HAVING COUNT(*) > 4; → devuelve los departamentos con más de cuatro empleados.

• SELECT idArticulo, COUNT(*) FROM Articulo GROUP BY idArticulo HAVING COUNT(*) > 1 → localiza identificadores duplicados.

• SELECT COUNT(DISTINCT NOMBRE) FROM PERSONAS → cuenta los nombres distintos (COUNT cuenta filas; DISTINCT elimina repetidos).

Operaciones JOIN

Un JOIN combina filas de dos o más tablas basándose en una columna relacionada, indicada con el operador ON. Según el estándar ANSI-SQL, los tipos de cláusula JOIN son: INNER, LEFT / RIGHT / FULL [OUTER] y CROSS (INSIDE no es un tipo válido de JOIN). El JOIN a secas equivale a INNER JOIN.

A diferencia del INNER JOIN (intersección respetada por ambas), con LEFT JOIN se da prioridad a la tabla izquierda y se busca en la derecha; con RIGHT JOIN, prioridad a la derecha buscando en la izquierda. Si en un LEFT JOIN un cliente no tiene pedidos, aparecerá igualmente con valores nulos en las columnas de pedidos. Un CROSS JOIN entre tablas de 4 y 3 filas produce 12 filas (4 × 3). Una auto-unión (self-join) une una tabla consigo misma (típicamente mediante un INNER JOIN con alias distintos) para relacionar filas de la propia tabla, como asociar cada trabajador con el nombre de su director.

Sintaxis de referencia:

SELECT * FROM tabla1 JOIN tabla2 ON tabla1.id = tabla2.id            -- INNER

SELECT * FROM tabla1 LEFT JOIN tabla2 ON tabla1.col = tabla2.col     -- LEFT

SELECT col(s) FROM table1 RIGHT JOIN table2 ON t1.col = t2.col       -- RIGHT

SELECT col(s) FROM table1 FULL OUTER JOIN table2 ON t1.col=t2.col    -- FULL

Operadores de conjuntos: UNION y UNION ALL

UNION combina dos o más conjuntos de resultados en una única tabla, pero solo si las consultas tienen el mismo número de columnas (y tipos compatibles). UNION elimina duplicados; UNION ALL los conserva (combina sin eliminar duplicados). Al ser una consulta, SELECT ... UNION ALL SELECT ... no modifica la base de datos.

Operadores, comodines y predicados

• Operadores lógicos: AND (todas las condiciones), OR (al menos una) y NOT (niega). Se pueden combinar en una misma condición respetando la precedencia de operadores (el orden en que se evalúan las condiciones; NOT > AND > OR, alterable con paréntesis).

• LIKE: usado con WHERE, busca por patrón mediante comodines % (cualquier secuencia de caracteres) y _ (un único carácter). Ejemplos: LIKE 'PAL%' → empieza por PAL; LIKE '_a%' → segundo carácter 'a'; LIKE 'a__%' → empieza por 'a' y tiene al menos 3 caracteres. A diferencia de = (comparación exacta), LIKE usa comodines.

• BETWEEN: selecciona valores dentro de un rango incluyendo los extremos inicial y final.

• EXISTS: prueba la existencia de cualquier registro en una subconsulta.

INSERT y valores NULL

La sintaxis básica es INSERT INTO tabla VALUES (valor1, valor2, ...). Se pueden insertar valores nulos con la palabra clave NULL. Si el número de valores no coincide con el número de columnas, la sentencia falla y no se inserta nada.

Funciones y dialectos concretos

• Oracle: SELECT SYSTIMESTAMP FROM DUAL; devuelve la fecha del sistema con segundos fraccionados y la zona horaria del servidor donde reside la BD (tipo TIMESTAMP WITH TIME ZONE).

• SUBSTRING / MID: SUBSTRING('SQL_Tutorial',5,3) devuelve 'Tut' (3 caracteres desde la posición 5). La función MID de MySQL extrae caracteres de un campo de texto.

• ROW_NUMBER(): función de ventana que asigna un número de fila secuencial a cada fila del resultado.

• Transact-SQL: admite subconsultas también en la cláusula FROM; sus operadores de comparación válidos incluyen =, <>, !=, <, >, <=, >= (=! no es válido). UNION es un operador de conjuntos, no una función de agregación.

• SQL-92 amplió el estándar con múltiples novedades (nuevos tipos de datos, JOIN explícitos, subconsultas ampliadas, etc.).

Fuentes

• PostgreSQL Global Development Group — Documentation 18: 7.2. Table Expressions (Joined Tables): https://www.postgresql.org/docs/current/queries-table-expressions.html

• PostgreSQL — Documentation: SELECT / Aggregate Functions: https://www.postgresql.org/docs/current/functions-aggregate.html

• Oracle — SQL Language Reference: SYSTIMESTAMP: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/SYSTIMESTAMP.html

• Oracle — Datetime Data Types and Time Zone Support: https://docs.oracle.com/en/database/oracle/oracle-database/21/nlspg/datetime-data-types-and-time-zone-support.html

• ISO/IEC 9075 (SQL) — norma internacional del lenguaje SQL: https://www.iso.org/standard/76583.html

• Microsoft Learn — Transact-SQL: Comparison Operators / Aggregate Functions: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/comparison-operators-transact-sql

Definición y control en SQL: DDL, DCL y TCL

El estándar ANSI/ISO SQL (formalizado como SQL-92, adoptado por ANSI e ISO en 1992, y continuado en ISO/IEC 9075) organiza las sentencias en varios sublenguajes. Además del DML (consulta y manipulación de datos, con SELECT, INSERT, UPDATE, DELETE), los tres sublenguajes de definición y control son:

• DDL (Data Definition Language, Lenguaje de Definición de Datos): define la estructura lógica de la base de datos.

• DCL (Data Control Language, Lenguaje de Control de Datos): gestiona los permisos y autorizaciones.

• TCL (Transaction Control Language, Lenguaje de Control de Transacciones): gestiona las transacciones.

El estándar SQL-92 define todos los elementos principales del lenguaje: sentencias, cláusulas y funciones. El módulo SQL/CLI (Call Level Interface) proporciona un conjunto de funciones de bajo nivel para acceder a bases de datos relacionales desde un lenguaje anfitrión.

DDL — Lenguaje de Definición de Datos

El DDL permite a los usuarios realizar tareas de definición de las estructuras que almacenarán los datos (tablas, vistas, índices, disparadores, etc.). Sus sentencias principales son CREATE, ALTER y DROP (también TRUNCATE):

ALTER TABLE modifica una tabla ya existente:

-- Añadir una columna

ALTER TABLE tabla_nombre ADD columna_nombre tipo_dato;

ALTER TABLE Clientes ADD telefono VARCHAR(10);

-- Modificar el tipo de una columna (sintaxis MySQL/Oracle)

ALTER TABLE tabla_nombre MODIFY COLUMN columna_nombre nuevo_tipo_dato;

ALTER TABLE UBICACIONES MODIFY PROVINCIA varchar(25);

-- Eliminar una columna

ALTER TABLE tabla_nombre DROP COLUMN columna_nombre;

DROP TABLE elimina la estructura de la tabla junto con todos los datos almacenados; al hacerlo también se eliminan los datos, las restricciones, los índices y los desencadenadores (triggers) asociados. En MySQL se pueden borrar varias tablas a la vez: DROP TABLE tabla1, tabla2, tabla3;. Si se intenta borrar una tabla que no existe sin la cláusula IF EXISTS, se muestra un mensaje de error y la ejecución del código restante se detiene; por eso se usa DROP TABLE IF EXISTS ... o, para bases de datos, DROP DATABASE [IF EXISTS] base_de_datos. Si se elimina una tabla de la que dependía una vista, esa vista debe eliminarse con DROP VIEW.

Una característica relevante es que PostgreSQL admite DDL transaccional: permite revertir con ROLLBACK sentencias CREATE y otros comandos DDL dentro de una transacción, algo que otros gestores (como Oracle o MySQL, donde el DDL provoca commit implícito) no permiten.

En el diseño de estructuras se distinguen las claves: la clave primaria identifica de forma única cada fila, es única y no admite valores nulos; la clave foránea (o ajena/externa) es una columna que hace referencia a la clave primaria de otra tabla. En las relaciones referenciales, el modo RESTRICT impide borrar una fila si existen filas relacionadas, mientras que CASCADE propaga la eliminación borrando también las filas relacionadas en otras tablas.

DCL — Lenguaje de Control de Datos

El modelo de autorización SQL define la forma en que los usuarios pueden acceder a los objetos de la base de datos. El DCL lo implementa con GRANT (otorgar) y REVOKE (revocar); en Transact-SQL existe además DENY, que es también una instrucción DCL.

• GRANT proporciona a los usuarios autorización para realizar una o más operaciones sobre una tabla. Sintaxis general:

GRANT lista-operaciones ON nombretabla TO lista-usuarios [WITH GRANT OPTION];

GRANT SELECT ON clientes TO jose;

GRANT ALL ON EMPLEADOS TO USUARIO1;

GRANT SELECT, UPDATE ON EMPLEADOS TO USUARIO1;

El privilegio se puede acotar a columnas concretas. Para permitir a un usuario actualizar solo la columna SALARIO sin poder dar de alta empleados:

GRANT SELECT, UPDATE(SALARIO) ON EMPLEADOS TO USUARIO1;

La cláusula WITH GRANT OPTION permite al usuario receptor otorgar a su vez esos privilegios a terceros.

• REVOKE elimina una autorización otorgada previamente por un GRANT. Sintaxis: REVOKE lista-operaciones ON nombretabla FROM lista-usuarios;. Para revocar la autorización de actualizar columnas específicas (o cualquier columna) se emplea igualmente REVOKE.

TCL — Control de transacciones (ACID)

Una transacción es un conjunto de órdenes que se ejecutan formando una unidad de trabajo atómica e indivisible; se diferencia de una simple consulta, que solo recupera datos. Que un sistema sea transaccional significa que puede mantener la integridad de los datos. Toda transacción debe cumplir las propiedades ACID:

• Atomicidad: la transacción se ejecuta completa o no se ejecuta en absoluto.

• Consistencia: lleva la base de datos de un estado válido a otro válido.

• Aislamiento (Isolation): las operaciones de otras transacciones no pueden acceder a los datos modificados por una transacción que aún no se ha completado.

• Durabilidad: una vez confirmada, la transacción persiste y puede recuperarse ante una falla del sistema.

Las sentencias de control son:

• COMMIT: confirma los cambios de forma permanente.

• ROLLBACK: revierte o restituye todos los cambios realizados desde el inicio de la transacción, o desde un punto de salvaguarda, restaurando el estado anterior. Se relaciona directamente con el control de integridad.

• SAVEPOINT: define un punto de salvaguarda dentro de una transacción (SAVEPOINT nombre_punto;), al que se puede volver con ROLLBACK TO.

Niveles de aislamiento y anomalías

El nivel de aislamiento determina la separación entre las modificaciones realizadas por transacciones concurrentes. El estándar SQL define tres fenómenos o anomalías:

El problema más importante es la lectura de resultados de transacciones no confirmadas (lecturas sucias), que provoca inconsistencia en los valores. Los cuatro niveles de aislamiento, de menor a mayor, y el fenómeno que cada uno resuelve son:

Read Committed es el nivel predeterminado en la mayoría de gestores relacionales (Oracle, PostgreSQL, SQL Server). Serializable es el más estricto: ofrece máxima integridad pero es el que más sacrifica rendimiento y concurrencia. Evitar las lecturas no repetibles es especialmente importante en claves primarias o ajenas, pues su alteración causaría gran pérdida de consistencia.

Fuentes

• PostgreSQL Global Development Group — Transaction Isolation (niveles y fenómenos SQL, nivel por defecto Read Committed): https://www.postgresql.org/docs/current/transaction-iso.html

• Oracle / MySQL — GRANT Statement y REVOKE Statement (sintaxis DCL): https://dev.mysql.com/doc/refman/8.0/en/grant.html

• Oracle — ALTER TABLE, DROP TABLE, TRUNCATE y SAVEPOINT/ROLLBACK (SQL Language Reference): https://docs.oracle.com/en/database/oracle/oracle-database/

• ISO/IEC 9075 (ANSI/ISO SQL, base del estándar SQL-92): https://www.iso.org/standard/63555.html

• PostgreSQL — DDL transaccional (Transactions): https://www.postgresql.org/docs/current/tutorial-transactions.html

Procedimientos almacenados y funciones

Un procedimiento almacenado (stored procedure) es un conjunto de sentencias SQL y de control de flujo, precompilado y guardado con nombre dentro del propio SGBD, que se puede invocar de forma repetida. Junto con las funciones almacenadas constituyen el mecanismo de programación del lado del servidor de los sistemas relacionales. El estándar ANSI/ISO SQL los recoge como SQL-invoked routines (SQL/PSM), pero cada fabricante añade su propio lenguaje procedural: PL/SQL en Oracle, Transact-SQL (T-SQL) en Microsoft SQL Server y el dialecto propio de MySQL/MariaDB.

Ventajas y desventajas

Entre las ventajas destacan: reducción del tráfico de red (se envía una sola llamada en lugar de muchas sentencias), reutilización y mantenimiento centralizado de la lógica, mejora del rendimiento por precompilación y planes de ejecución cacheados, y refuerzo de la seguridad (se conceden permisos de ejecución sin exponer las tablas).

Como desventajas reconocidas se citan: la lógica de negocio queda dispersa entre la aplicación y la base de datos, la portabilidad entre SGBD es baja (cada dialecto es distinto), la escalabilidad se ve limitada porque la carga recae en el servidor de BD, y la depuración (debugging) y el perfilado (profiling) resultan difíciles. Por tanto, afirmar que "los procedimientos almacenados pueden ser depurados y perfilados fácilmente" NO es una desventaja, sino una afirmación falsa: precisamente la dificultad de depuración es una de sus limitaciones.

Funciones frente a procedimientos

Una diferencia real entre funciones y procedimientos en SQL Server es que las funciones devuelven un valor y pueden usarse dentro de consultas. NO es cierto que "las funciones puedan utilizarse para modificar los datos de una tabla": las funciones no pueden ejecutar sentencias DML que alteren el estado de la base de datos.

PL/SQL (Oracle)

En Oracle un procedimiento se crea con CREATE [OR REPLACE] PROCEDURE. Los modos de parámetro válidos son únicamente IN (entrada, valor por defecto), OUT (salida) e IN OUT (entrada/salida). RETURN NO es un modo de parámetro válido (RETURN es la cláusula que devuelve el valor de una función, no un tipo de parámetro).

PL/SQL distingue excepciones predefinidas (declaradas globalmente en el paquete STANDARD), excepciones definidas por el usuario y excepciones internas no predefinidas. Ejemplos de excepciones predefinidas reales son: NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, INVALID_CURSOR, INVALID_NUMBER, VALUE_ERROR, DUP_VAL_ON_INDEX, CURSOR_ALREADY_OPEN y ROWTYPE_MISMATCH. En cambio, INVALID_INDEX NO es una excepción de PL/SQL, y TYPE_MISMATCH tampoco existe como excepción de Oracle (la análoga es ROWTYPE_MISMATCH).

Un procedimiento almacenado de Oracle puede ejecutarse de varias formas válidas: con EXECUTE nombre; (o EXEC) desde SQL*Plus, mediante una llamada anónima BEGIN nombre; END;, desde otro procedimiento o función, o invocado desde una aplicación externa a través de la interfaz nativa (OCI, JDBC, ODBC). NO es una forma válida ejecutarlo "desde una aplicación externa que se comunica con Oracle a través del protocolo HTTP": HTTP no es el mecanismo de invocación directa de un procedimiento en Oracle.

Transact-SQL (SQL Server)

T-SQL es un lenguaje procedural y declarativo que extiende SQL con variables, control de flujo, manejo de errores (TRY...CATCH) y creación de objetos. Las variables se declaran con DECLARE @nombre tipo, opcionalmente con un valor inicial, y se asignan con SET o SELECT. Por ejemplo:

DECLARE @eliminacion INT = 0;

SET @eliminacion = (SELECT COUNT(*) FROM Eliminaciones

WHERE fecha = CONVERT(date, GETDATE()));

Este fragmento cuenta el número de eliminaciones registradas en la tabla Eliminaciones con fecha igual a la del día actual (GETDATE() convertida a tipo date), almacenando el resultado en la variable.

Un procedimiento con parámetro se crea así:

CREATE PROCEDURE getClientesPorPais @Pais nvarchar(30)

AS

SELECT * FROM Pais p, Cliente c

WHERE c.idPais = p.idPais AND c.idPais = @Pais

GO;

Los parámetros de entrada en T-SQL se prefijan con @. Entre las características de T-SQL están el control de flujo, las transacciones y las funciones definidas por el usuario; sin embargo T-SQL NO permite ejecutar código C# directamente dentro de las consultas. (La integración CLR permite ensamblados .NET como objetos separados, pero no es "C# dentro de las consultas".)

MySQL / MariaDB

En MySQL los procedimientos se crean con CREATE PROCEDURE y se ejecutan con la sentencia CALL <nombre_procedimiento> (a diferencia de EXEC de SQL Server). Dentro del cuerpo (BEGIN ... END) las variables locales se declaran con DECLARE indicando nombre, tipo de dato y opcionalmente un valor por defecto (DEFAULT). En el tipo de dato, la longitud máxima solo debe especificarse para tipos como CHAR y VARCHAR (p. ej. DECLARE nombre VARCHAR(50) DEFAULT '';), mientras que tipos como INT no la requieren. El patrón general —palabra clave declare (o DECLARE) seguida del nombre de la variable, su tipo de dato y su valor por defecto— es común a los distintos dialectos procedurales.

Sintaxis extendida, eventos y disparadores

La sintaxis extendida de SQL es la ampliación del SQL estándar con capacidades procedurales que permite, entre otras cosas, definir procedimientos almacenados y disparadores (triggers). Los disparadores son rutinas que se ejecutan automáticamente ante eventos DML (INSERT, UPDATE, DELETE), y los eventos (event scheduler) permiten programar la ejecución de código en momentos determinados, completando el modelo de programación del lado del servidor descrito en el epígrafe oficial.

Fuentes

• Oracle Database PL/SQL Language Reference (19c) — Predefined Exceptions: https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/predefined-exceptions.html

• Oracle Database PL/SQL Language Reference (19c) — Subprogram Parameters (modos IN, OUT, IN OUT): https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/subprogram-parameters.html

• Oracle Database — Handling PL/SQL Errors: https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/07_errs.htm

• Microsoft Learn — CREATE PROCEDURE (Transact-SQL): https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql

• Microsoft Learn — DECLARE @local_variable (Transact-SQL): https://learn.microsoft.com/en-us/sql/t-sql/language-elements/declare-local-variable-transact-sql

• MySQL 8.0 Reference Manual — CALL Statement y DECLARE en rutinas almacenadas: https://dev.mysql.com/doc/refman/8.0/en/call.html

• ISO/IEC 9075 (SQL/PSM) — SQL-invoked routines.

Disparadores (triggers) y eventos

Un disparador o trigger es un objeto con nombre almacenado en una base de datos que se asocia a una tabla (o vista) y se activa automáticamente cuando ocurre un evento en particular sobre esa tabla. A diferencia de un procedimiento almacenado, que se invoca de forma explícita mediante CALL/EXECUTE, el trigger no se llama: el propio gestor lo dispara cuando se produce el evento que lo define. Es la herramienta idónea cuando queremos que, por ejemplo, cada vez que se dé de alta un pedido se ejecute automáticamente y en ese mismo momento código que compruebe el stock: eso se resuelve con un disparador (TRIGGER), no con una consulta ni con una tarea programada externa.

Eventos que activan un trigger

Los triggers responden a eventos DML (Data Manipulation Language) sobre la tabla asociada. Los tres eventos posibles son:

• INSERT — inserción de filas.

• UPDATE — modificación de filas (opcionalmente restringible a columnas concretas con UPDATE OF columna).

• DELETE — borrado de filas.

Un trigger AFTER INSERT, por tanto, se desencadena por un evento DML posterior a la inserción de filas en la tabla.

La creación de una tabla (CREATE TABLE) NO es un evento que active un trigger DML: es una operación DDL (Data Definition Language). Algunos motores ofrecen DDL triggers como extensión propietaria, pero no forman parte de los eventos DML estándar sobre una tabla.

Momento de activación y granularidad

El estándar ANSI/ISO SQL distingue dos ejes ortogonales:

1. Momento (timing):

• BEFORE — la acción se ejecuta antes de aplicar el cambio; permite validar o modificar los valores entrantes.

• AFTER — la acción se ejecuta después de aplicar el cambio (y posiblemente tras aplicar las restricciones de integridad correspondientes).

• INSTEAD OF — sustituye por completo la operación.

2. Granularidad (nivel):

• FOR EACH ROW (nivel fila) — la acción se ejecuta una vez por cada fila afectada por la sentencia.

• FOR EACH STATEMENT (nivel sentencia) — la acción se ejecuta una sola vez por sentencia, con independencia del número de filas afectadas.

Combinando ambos ejes: un trigger AFTER ROW ejecuta su acción después de modificar cada fila afectada por la sentencia disparadora, posiblemente aplicando las restricciones de integridad apropiadas; y un trigger AFTER STATEMENT se ejecuta una sola vez después de completarse toda la serie de operaciones DML de la sentencia.

Sintaxis ANSI SQL

La forma canónica de la sentencia de creación es:

CREATE TRIGGER nombre { BEFORE | AFTER | INSTEAD OF }

{ INSERT | DELETE | UPDATE [ OF columna [, ...] ] } ON tabla

[ REFERENCING { OLD | NEW } [ ROW | TABLE ] [AS] alias ]

[ FOR EACH { ROW | STATEMENT } ]

[ WHEN (condición) ] cuerpo;

Así, para validar un cambio de valor del campo Salario sobre la tabla Empleados, una forma válida de comenzar el trigger es:

CREATE TRIGGER Validar_salario BEFORE UPDATE OF Salario ON Empleados

Y para validar un cambio antes del evento sobre la tabla Departamento (sin restringir a columna):

CREATE TRIGGER Validar_cambio BEFORE UPDATE ON Departamento

Dentro de un trigger de nivel fila se accede a los valores mediante los identificadores de transición NEW (fila nueva/entrante en INSERT y UPDATE) y OLD (fila previa en UPDATE y DELETE). Por eso es incorrecto afirmar que la acción de un trigger solo puede referirse a valores nuevos que se insertaron: puede referirse tanto a los valores nuevos (NEW) como a los antiguos (OLD).

Ejemplo BEFORE INSERT que corrige valores

CREATE TRIGGER verificarEdad BEFORE INSERT ON persona

FOR EACH ROW

IF NEW.edad < 0 THEN SET NEW.edad = 0; END IF;

Este trigger, al ser BEFORE y modificar NEW.edad, altera el valor antes de escribirlo. Ante los INSERT de (-20,'Julia'), (30,'Jaime') y (0,'Alba'), la columna edad queda: Julia 0 (el −20 se corrige a 0), Jaime 30 (no cumple la condición, se conserva) y Alba 0 (ya era 0). La capacidad de reescribir NEW es exclusiva de los triggers BEFORE de nivel fila.

Triggers INSTEAD OF

Un trigger INSTEAD OF tiene como propósito reemplazar la operación DML original por un conjunto de acciones personalizadas. Cuando se dispara, la sentencia DML que lo activó no se ejecuta: el trigger la sustituye por completo. Como consecuencia, si un trigger INSTEAD OF no realiza ninguna acción, el efecto neto es que la operación DML original no se ejecuta (no se produce ni el INSERT/UPDATE/DELETE original ni acción sustitutiva alguna). Su uso típico es habilitar operaciones DML sobre vistas que de otro modo no serían actualizables.

Gestión de errores y transaccionalidad

Los triggers se ejecutan dentro del contexto transaccional de la sentencia que los dispara. Por tanto, un error durante la ejecución de un trigger cancela automáticamente (revierte) la operación que lo disparó: no hay que programar manualmente la cancelación. Es por ello incorrecto afirmar lo contrario (que el error no cancela y que la cancelación haya que programarla). Esta atomicidad es una de las garantías básicas del modelo de disparadores.

Buenas prácticas de rendimiento

Como el trigger se ejecuta de forma síncrona dentro de la transacción, un mal diseño penaliza cada operación DML. Recomendaciones válidas: mantener el cuerpo del trigger lo más ligero posible, evitar lógica y consultas costosas, restringir el disparo con UPDATE OF columna o cláusulas WHEN, y no encadenar triggers innecesariamente. NO es una técnica recomendada (ni compatible con su naturaleza transaccional síncrona) ejecutar el trigger en segundo plano de forma asíncrona: el trigger forma parte de la transacción y no puede desacoplarse a un proceso asíncrono sin romper su semántica.

Nota sobre tareas SQL en Oracle Cloud Infrastructure

En el ámbito de OCI, una tarea de SQL puede ejecutarse de varias formas (bajo demanda/de forma inmediata y de forma programada según una planificación), pero no constituye una forma válida ejecutarla desde un archivo SQL que contenga la definición de la tarea: la tarea se define y gestiona como objeto del servicio, no se lanza cargando su definición desde un fichero.

Fuentes

• ISO/IEC 9075 (SQL:2016), Information technology — Database languages — SQL, Part 2: Foundation (define CREATE TRIGGER, BEFORE/AFTER/INSTEAD OF, FOR EACH ROW/STATEMENT, REFERENCING OLD/NEW). https://www.iso.org/standard/63556.html

• Microsoft Learn — DML Triggers (SQL Server). https://learn.microsoft.com/en-us/sql/relational-databases/triggers/dml-triggers

• Microsoft Learn — CREATE TRIGGER (Transact-SQL). https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql

• Oracle — Database PL/SQL Language Reference: CREATE TRIGGER / INSTEAD OF triggers. https://docs.oracle.com/en/database/oracle/oracle-database/

• PostgreSQL Documentation — CREATE TRIGGER. https://www.postgresql.org/docs/current/sql-createtrigger.html

• SQLite — CREATE TRIGGER (UPDATE OF column, FOR EACH ROW). https://www.sqlite.org/lang_createtrigger.html