Disparadores de PostgresSQL

Un disparador no es otra cosa que una acción definida en una tabla de nuestra base de datos y ejecutada automáticamente por una función programada por nosotros. Esta acción se activará, segun la definamos, cuando realicemos un INSERT, un UPDATE ó un DELETE en la susodicha tabla.

 

Un disparador se puede definir de las siguientes maneras:

 

Para que ocurra ANTES de cualquier INSERT,UPDATE ó DELETE

Para que ocurra DESPUES de cualquier INSERT,UPDATE ó DELETE

Para que se ejecute una sola vez por comando SQL (statement-level trigger)

Para que se ejecute por cada linea afectada por un comando SQL (row-level trigger)

Esta es la definición del comando SQL que se puede utilizar para definir un disparador en una tabla.

 

Sintaxis

 

CREATE TRIGGER nombre { BEFORE | AFTER } { INSERT | UPDATE | DELETE [ OR … ] }

ON tabla [ FOR [ EACH ] { ROW | STATEMENT } ]

EXECUTE PROCEDURE nombre de funcion ( argumentos )

 

 

Veamos por partes que quiere decir cada una de las partes que conforman la sentencia:

 

	Significado
CREATE TRIGGER	Palabra clave para crear un nuevo trigger.
BEFORE	Indica que el trigger se dispara antes de una acción.
AFTER	Indica que el trigger se dispara después de una acción.
INSERT	Indica que una acción puede ser de insertar valores.
UPDATE	Indica que una acción puede ser de actualizar valores.
DELETE	Indica que una acción puede ser de borrar valores.
EXECUTE PROCEDURE	Indica que se va a ejecutar un procedimiento después de una acción.

 

Antes de definir el disparador tendremos que definir el procedimiento almacenado que se ejecutará cuando nuestro disparador se active.

 

Características y reglas a seguir

A continuación tenemos algunas de las características y reglas más importantes a tener en cuenta, cuando definamos un disparador y/ó programemos un procedimiento almacenado que se vaya a utilizar por un disparador:

 

1. El procedimiento almacenado que se vaya a utilizar por el disparador debe de definirse e instalarse antes de definir el propio disparador.

 

2. Un procedimiento que se vaya a utilizar por un disparador no puede tener argumentos y tiene que devolver el tipo “trigger”.

 

3. Un mismo procedimiento almacenado se puede utilizar por múltiples disparadores en diferentes tablas.

 

4. Procedimientos almacenados utilizados por disparadores que se ejecutan una sola vez per comando SQL (statement-level) tienen que devolver siempre NULL.

 

5. Procedimientos almacenados utilizados por disparadores que se ejecutan una vez per linea afectada por el comando SQL (row-level) pueden devolver una fila de tabla.

 

6. Procedimientos almacenados utilizados por disparadores que se ejecutan una vez per fila afectada por el comando SQL (row-level) y ANTES de ejecutar el comando SQL que lo lanzó, pueden:

Retornar NULL para saltarse la operación en la fila afectada.

Ó devolver una fila de tabla (RECORD)

 

7. Procedimientos almacenados utilizados por disparadores que se ejecutan DESPUES de ejecutar el comando SQL que lo lanzó, ignoran el valor de retorno, asi que pueden retornar NULL sin problemas.

 

8. En resumen, independendientemente de como se defina un disparador, el procedimiento almacenado utilizado por dicho disparador tiene que devolver ó bien NULL, ó bien un valor RECORD con la misma estructura que la tabla que lanzó dicho disparador.

 

9. Si una tabla tiene más de un disparador definido para un mismo evento (INSERT,UPDATE,DELETE), estos se ejecutarán en orden alfabético por el nombre del disparador. En el caso de disparadores del tipo ANTES / row-level, la file retornada por cada disparador, se convierte en la entrada del siguiente. Si alguno de ellos retorna NULL, la operación será anulada para la fila afectada.

 

10. Procedimientos almacenados utilizados por disparadores pueden ejecutar sentencias SQL que a su vez pueden activar otros disparadores. Esto se conoce como disparadores en cascada. No existe límite para el número de disparadores que se pueden llamar pero es responsabilidad del programador el evitar una recursión infinita de llamadas en la que un disparador se llame asi mismo de manera recursiva.

Otra cosa que tenemos que tener en cuenta es que, por cada disparador que definamos en una tabla, nuestra base de datos tendrá que ejecutar la función asociada a dicho disparador. El uso de disparadores de manera incorrecta ó inefectiva puede afectar significativamente al rendimiento de nuestra base de datos.

 

Variables especiales en PL/pgSQL

Cuando una función escrita en PL/pgSQL es llamada por un disparador tenemos ciertas variable especiales disponibles en dicha función. Estas variables son las siguientes:

 

NEW

Tipo de dato RECORD; Variable que contiene la nueva fila de la tabla para las operaciones INSERT/UPDATE en disparadores del tipo row-level. Esta variable es NULL en disparadores del tipo statement-level.

 

OLD

Tipo de dato RECORD; Variable que contiene la antigua fila de la tabla para las operaciones UPDATE/DELETE en disparadores del tipo row-level. Esta variable es NULL en disparadores del tipo statement-level.

 

TG_NAME

Tipo de dato name; variable que contiene el nombre del disparador que está usando la función actualmente.

 

TG_WHEN

Tipo de dato text; una cadena de texto con el valor BEFORE o AFTER dependiendo de como el disparador que está usando la función actualmente ha sido definido

 

TG_LEVEL

Tipo de dato text; una cadena de texto con el valor ROW o STATEMENT dependiendo de como el disparador que está usando la función actualmente ha sido definido

 

TG_OP

Tipo de dato text; una cadena de texto con el valor INSERT, UPDATE o DELETE dependiendo de la operación que ha activado el disparador que está usando la función actualmente.

 

TG_RELID

Tipo de dato oid; el identificador de objeto de la tabla que ha activado el disparador que está usando la función actualmente.

 

TG_RELNAME

Tipo de dato name; el nombre de la tabla que ha activado el disparador que está usando la función actualmente. Esta variable es obsoleta y puede desaparacer en el futuro. Usar TG_TABLE_NAME.

 

TG_TABLE_NAME

Tipo de dato name; el nombre de la tabla que ha activado el disparador que está usando la función actualmente.

 

TG_TABLE_SCHEMA

Tipo de dato name; el nombre de la schema de la tabla que ha activado el disparador que está usando la función actualmente.

 

TG_NARGS

Tipo de dato integer; el número de argumentos dados al procedimiento en la sentencia CREATE TRIGGER.

 

TG_ARGV[]

Tipo de dato text array; los argumentos de la sentencia CREATE TRIGGER. El índice empieza a contar desde 0. Indices inválidos (menores que 0 ó mayores/iguales que tg_nargs) resultan en valores nulos.

 

CREATE TABLE numeros(

numero bigint NOT NULL,

cuadrado bigint,

cubo bigint,

raiz2 real,

raiz3 real,

PRIMARY KEY (numero)

);

 

 

CREATE OR REPLACE FUNCTION proteger_datos() RETURNS TRIGGER AS $proteger_datos$

DECLARE

BEGIN

 

—

— Esta funcion es usada para proteger datos en un tabla

— No se permitira el borrado de filas si la usamos

— en un disparador de tipo BEFORE / row-level

—

 

RETURN NULL;

END;

$proteger_datos$ LANGUAGE plpgsql;

 

 

CREATE TRIGGER proteger_datos BEFORE DELETE

ON numeros FOR EACH ROW

EXECUTE PROCEDURE proteger_datos();

 

 

CREATE OR REPLACE FUNCTION rellenar_datos() RETURNS TRIGGER AS $rellenar_datos$

DECLARE

BEGIN

 

NEW.cuadrado := power(NEW.numero,2);

NEW.cubo := power(NEW.numero,3);

NEW.raiz2 := sqrt(NEW.numero);

NEW.raiz3 := cbrt(NEW.numero);

 

RETURN NEW;

END;

$rellenar_datos$ LANGUAGE plpgsql;

 

CREATE TRIGGER rellenar_datos BEFORE INSERT OR UPDATE

ON numeros FOR EACH ROW

EXECUTE PROCEDURE rellenar_datos();

 

 

SELECT * from numeros;

INSERT INTO numeros (numero) VALUES (2);

 

SELECT * from numeros;

INSERT INTO numeros (numero) VALUES (3);

 

SELECT * from numeros;

 

UPDATE numeros SET numero = 4 WHERE numero = 3;

SELECT * from numeros;

 

 

DROP TRIGGER proteger_datos ON numeros;

 

DROP TRIGGER rellenar_datos ON numeros;

 

CREATE OR REPLACE FUNCTION proteger_y_rellenar_datos() RETURNS TRIGGER AS $proteger_y_rellenar_datos$

DECLARE

BEGIN

 

IF (TG_OP = ‘INSERT’ OR TG_OP = ‘UPDATE’ ) THEN

 

NEW.cuadrado := power(NEW.numero,2);

NEW.cubo := power(NEW.numero,3);

NEW.raiz2 := sqrt(NEW.numero);

NEW.raiz3 := cbrt(NEW.numero);

RETURN NEW;

 

ELSEIF (TG_OP = ‘DELETE’) THEN

RETURN NULL;

 

END IF;

END;

$proteger_y_rellenar_datos$ LANGUAGE plpgsql;

 

 

CREATE TRIGGER proteger_y_rellenar_datos BEFORE INSERT OR UPDATE OR DELETE

ON numeros FOR EACH ROW

EXECUTE PROCEDURE proteger_y_rellenar_datos();

 

 

INSERT INTO numeros (numero) VALUES (5);

INSERT INTO numeros (numero) VALUES (6);

UPDATE numeros SET numero = 10 WHERE numero = 6;

DELETE FROM numeros where numero =10;

 

 

 

CREATE TABLE cambios(

timestamp_ TIMESTAMP WITH TIME ZONE default NOW(),

nombre_disparador text,

tipo_disparador text,

nivel_disparador text,

comando text

);

 

 

CREATE OR REPLACE FUNCTION grabar_operaciones() RETURNS TRIGGER AS $grabar_operaciones$

DECLARE

BEGIN

 

INSERT INTO cambios (

nombre_disparador,

tipo_disparador,

nivel_disparador,

comando)

VALUES (

TG_NAME,

TG_WHEN,

TG_LEVEL,

TG_OP

);

 

RETURN NULL;

END;

$grabar_operaciones$ LANGUAGE plpgsql;

 

 

CREATE TRIGGER grabar_operaciones AFTER INSERT OR UPDATE OR DELETE

ON numeros FOR EACH STATEMENT

EXECUTE PROCEDURE grabar_operaciones();

 

 

INSERT INTO numeros (numero) VALUES (100);

 

SELECT * from numeros;

SELECT * from cambios;

 

UPDATE numeros SET numero = 1000 WHERE numero = 100;

SELECT * from numeros;

SELECT * from cambios;

 

 

DELETE FROM numeros where numero =1000;

SELECT * from numeros;

SELECT * from cambios;

 

 

Ejemplo de validacion de datos con triggers

CREATE TABLE emp (

empname text,

salary integer,

last_date timestamp,

last_user text

);

 

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$

BEGIN

— Check that empname and salary are given

IF NEW.empname IS NULL THEN

RAISE EXCEPTION ’empname cannot be null’;

END IF;

IF NEW.salary IS NULL THEN

RAISE EXCEPTION ‘% cannot have null salary’, NEW.empname;

END IF;

 

— Who works for us when she must pay for it?

IF NEW.salary < 0 THEN

RAISE EXCEPTION ‘% cannot have a negative salary’, NEW.empname;

END IF;

 

— Remember who changed the payroll when

NEW.last_date := current_timestamp;

NEW.last_user := current_user;

RETURN NEW;

END;

$emp_stamp$ LANGUAGE plpgsql;

 

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp

FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

 

INSERT INTO emp (empname) VALUES (‘Juan’);

 

INSERT INTO emp (salary) VALUES (100);

 

INSERT INTO emp (empname,salary) VALUES (‘Juan’,100);

 

 

CREATE TABLE users (id int PRIMARY KEY, name varchar(256));

CREATE TABLE address (id_user int, address text);

 

CREATE OR REPLACE FUNCTION delete_address() RETURNS TRIGGER AS $$

BEGIN

DELETE FROM address WHERE address.id_user = OLD.id;

RETURN OLD;

END;

$$ LANGUAGE ‘plpgsql’;

 

CREATE TRIGGER delete_user_address

BEFORE DELETE ON users

FOR EACH ROW EXECUTE PROCEDURE delete_address();

 

INSERT INTO users VALUES (2, ‘Michael P’);

INSERT INTO address VALUES (2, ‘Work in Tokyo, Japan’);

INSERT INTO address VALUES (2, ‘Live in San Francisco, California’);

 

DELETE FROM users WHERE id=2;

 

 

 

CREATE TABLE emp_table ( empid int, empname name, salary int );

CREATE TABLE backup_tbl ( empid int, empname name, salary int, operation varchar(25) );

 

CREATE FUNCTION ins_function() RETURNS trigger AS $$

BEGIN

IF tg_op = ‘DELETE’ THEN

INSERT INTO backup_tbl(empid, empname, salary, operation)

VALUES (old.empid, old.empname, old.salary, tg_op);

RETURN old;

END IF;

IF tg_op = ‘INSERT’ THEN

INSERT INTO backup_tbl(empid, empname, salary, operation)

VALUES (new.empid, new.empname, new.salary, tg_op);

RETURN new;

END IF;

IF tg_op = ‘UPDATE’ THEN

INSERT INTO backup_tbl(empid, empname, salary, operation)

VALUES (old.empid, old.empname, old.salary, tg_op);

RETURN new;

END IF;

END

$$ LANGUAGE plpgsql;

 

CREATE TRIGGER audit_ins AFTER INSERT OR DELETE OR UPDATE

ON emp_table FOR each ROW

EXECUTE PROCEDURE ins_function();

 

 

INSERT INTO emp_table (empid, empname, salary) values (101, ‘sathiya’, ‘3000’);

INSERT INTO emp_table (empid, empname, salary) values (102, ‘michael’, ‘1200’);

UPDATE emp_table SET salary = ‘2500’ where empid = ‘101’;

INSERT INTO emp_table (empid, empname, salary) values (103, ‘john’, ‘1500’);

INSERT INTO emp_table (empid, empname, salary) values (104, ‘peter’, ‘1800’);

UPDATE emp_table SET salary = ‘3400’ where empid = ‘104’;

DELETE FROM emp_table WHERE empid = ‘101’;

UPDATE emp_table SET salary = ‘800’ where empid = ‘103’;

INSERT INTO emp_table (empid, empname, salary) values (105, ‘maria’, ‘2200’);

UPDATE emp_table SET salary = ‘8000’ where empid = ‘103’;

UPDATE emp_table SET salary = ‘4400’ where empid = ‘105’;

---

Si quieres seguir aprendiendo con nosotros, puedes ingresar a nuestros