PDF de programación - PostgreSQL Módulo 1 - Triggers

GoBack

PostgreSQL Módulo 1 - Triggers

Rodrigo Soliz Rocabado ([email protected])

June 14, 2007

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 1

Triggers

Triggers

Ejecución de un trigger

Definición de un trigger

Partes de un trigger

Definición formal del trigger

Nuestro primer trigger

Que es lo que queremos?

Variables del sistema

Variables del trigger

Función trigger

Otro trigger

Triggers

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 2

Triggers

Hemos visto las ventajas que la funciones nos proporcionan al simplificar acciones en
nuestra base de datos.
Pero estas acciones requieren la intervención de una persona encargada de ejecutar las
funciones cuando se requiera de su actuación.
Los Triggers al contrario son funciones que se ejecutan de forma automática en respuesta
a ciertos eventos que ocurren en la base de datos.

Triggers

Triggers

Ejecución de un trigger

Definición de un trigger

Partes de un trigger

Definición formal del trigger

Nuestro primer trigger

Que es lo que queremos?

Variables del sistema

Variables del trigger

Función trigger

Otro trigger

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 3

Ejecución de un trigger

Cuando se ejecuta un trigger?

Antes o después de una inserción (INSERT)

Antes o después de una actualización (UPDATE)

Antes o después de un borrado (DELETE)

Triggers

Triggers

Ejecución de un trigger

Definición de un trigger

Partes de un trigger

Definición formal del trigger

Nuestro primer trigger

Que es lo que queremos?

Variables del sistema

Variables del trigger

Función trigger

Otro trigger

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 4

Definición de un trigger

Triggers

Triggers

Ejecución de un trigger

Definición de un trigger

Partes de un trigger

Definición formal del trigger

Nuestro primer trigger

Que es lo que queremos?

Variables del sistema

Variables del trigger

Función trigger

Otro trigger

La definición de un trigger consta de dos partes,

1. La definición de la función asociada al trigger que es la que ejecuta la acción en

respuesta al evento (INSERT, UPDATE, DELETE)

2. La definición formal de trigger que indicará:

(a) De que tabla se esperarán los eventos

(b) A que tipo de evento se responderá

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 5

Triggers

Triggers

Ejecución de un trigger

Definición de un trigger

Partes de un trigger

Definición formal del trigger

Nuestro primer trigger

Que es lo que queremos?

Variables del sistema

Variables del trigger

Función trigger

Otro trigger

Partes de un trigger

Una función tipo trigger es similar a una función normal, salvo por unos detalles:

CREATERREACEFUCT

bef ciaa
aa

RETURSigge
AS
DECARE
vaiabe;
vaiabe;
BEG
eecia;
eecia;
RETURe;
ED;ag ageg;

Primero, el tipo de retorno ya no es un tipo normal de PostgreSQL, sino el tipo especial
trigger.
Segundo, dependiendo de las acciones del trigger, el parámetro en el comando RETURN
puede variar, eso lo veremos mas adelante :-)

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 6

Definición formal del trigger

Luego de elaborada la función trigger, debemos definir formalmente el trigger de este

modo:CREATERREACETR
GGER
beigge
[AFTER|BEFRE][
SERT|UDATE|DEETE]

beaba
FREAC[RW|STATEET]
EXECUTERCEDUREf ci;

Triggers

Triggers

Ejecución de un trigger

Definición de un trigger

Partes de un trigger

Definición formal del trigger

Nuestro primer trigger

Que es lo que queremos?

Variables del sistema

Variables del trigger

Función trigger

Otro trigger

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 7

Explicación

Triggers

Triggers

Ejecución de un trigger

Definición de un trigger

Partes de un trigger

Definición formal del trigger

Nuestro primer trigger

Que es lo que queremos?

Variables del sistema

Variables del trigger

Función trigger

Otro trigger

CREATE TRIGGER nombretrigger = Creación del trigger

[AFTER | BEFORE ] = Antes o después del evento

[INSERT | UPDATE | DELETE ] = El tipo de evento al que se responderá

ON nombretabla = De que tabla se esperarán los eventos

FOR EACH [ROW | STATEMENT ] = Para cada fila o para cada sentencia

EXECUTE PROCEDURE función = La función que se va a ejecutar (con sus parámetros

si es que los necesitara)

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 8

Nuestro primer trigger

Hagamos un ejemplo para ver las cosas un poco mas claras. Tomemos otra vez nuestra
tabla item:

CREATETABEie

eiaTU
ie
_id
vacha150TU

be
vacha100TU
i
i4TUDEFAUT0
caidad
eci_c
a

eic72TU

eic72TU
eci_vea
CSTRA
Tie
_id_kR
ARYEYie
_id
;

Triggers

Triggers

Ejecución de un trigger

Definición de un trigger

Partes de un trigger

Definición formal del trigger

Nuestro primer trigger

Que es lo que queremos?

Variables del sistema

Variables del trigger

Función trigger

Otro trigger

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 9

Que es lo que queremos?

Triggers

Triggers

Ejecución de un trigger

Definición de un trigger

Partes de un trigger

Definición formal del trigger

Nuestro primer trigger

Que es lo que queremos?

Variables del sistema

Variables del trigger

Función trigger

Otro trigger

Nuestra tabla item tiene la capacidad de almacenar la información mas actual e
importante para la tienda de computadoras, pero hay otro tipo de información que no es
capaz de almacenar (por lo menos por ahora), la información del cambio de datos.
Nuestra tabla item no tiene memoria de los cambios que han sufrido sus tuplas, si alguien
viene y cambia el precio de venta de un artículo, no hay forma de obtener el precio
anterior.
Si alguien borra un artículo, tampoco sabremos que artículo ha sido borrado ni por quien.
Esto nos lleva a la primera gran área de utilización de los triggers, la auditoría de tablas.

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 10

Que es lo que queremos?

Triggers

Triggers

Ejecución de un trigger

Definición de un trigger

Partes de un trigger

Definición formal del trigger

Nuestro primer trigger

Que es lo que queremos?

Variables del sistema

Variables del trigger

Función trigger

Otro trigger

Entonces cuales son las acciones que debe realizar nuestro trigger?

Necesitamos llevar un registro de todos los cambios relevantes en nuestra tabla

item, para empezar queremos tener un seguimiento de los cambios en los precios
de los items.

También queremos almacenar en algún lugar todos aquellos items que hayan sido

eliminados, también queremos guardar sus respectivos historiales de cambios.

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 11

Variables del sistema

Triggers

Triggers

Ejecución de un trigger

Definición de un trigger

Partes de un trigger

Definición formal del trigger

Nuestro primer trigger

Que es lo que queremos?

Variables del sistema

Variables del trigger

Función trigger

Otro trigger

Antes de empezar a definir el trigger es necesario conocer una cuantas variables por
defecto que nos provee PostgreSQL:

current_user = el nombre del usuario que esta actualmente conectado a la base de

datos y que ejecuta las sentencias.

current_date = La fecha actual (del servidor, no del cliente)

current_time = La hora actual

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 12

Variables del trigger

Triggers

Triggers

Ejecución de un trigger

Definición de un trigger

Partes de un trigger

Definición formal del trigger

Nuestro primer trigger

Que es lo que queremos?

Variables del sistema

Variables del trigger

Función trigger

Otro trigger

PostgreSQL también maneja unas cuantas variables al momento de ejecutar un trigger,
estas son:

NEW = Variable compuesta que almacena los nuevos valores de la tupla que se esta

modificando

OLD = Variable compuesta que almacena los valores antiguos de la tupla que se esta

modificando

TG_OP = Variable tipo string que indica que tipo de evento está ocurriendo (INSERT,

UPDATE, DELETE)

TG_ARGV = Variable tipo arreglo que almacena los parametros de la función del trigger,

podemos accederlos de la forma TG_ARGV[0], TG_ARGV[1], etc.

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 13

Tabla adicional

Triggers

Triggers

Ejecución de un trigger

Definición de un trigger

Partes de un trigger

Definición formal del trigger

Nuestro primer trigger

Que es lo que queremos?

Variables del sistema

Variables del trigger

Función trigger

Otro trigger

Como último paso antes de empezar a definir el trigger, debemos crear una tabla que vaya
a almacenar los cambios de precio en nuestra tabla item, la llamaremos
item_actualizado.

CREATETABEie
_ac aizad
ie
_id
i4TU

be
vacha150TU
eci_aei

eic72TU

eic72TU
eci_ac aizad
vacha100TU
a 
daeTU
fecha_ca
bi
CSTRA
Tie
_id_fkFRE
GEYie
_idREFERECESie
ie
_id
;

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 14

Función trigger

CREATEFUCT
ie
_ac aizad_i
RETURSigge
AS
BEG

FTG_=UDATETE

SERT
Tie
_ac aizadVAUES
D.ie
_idD.
beD.eci_vea
EW.eci_veac e_ ec e_dae;
ED
F;
RETURU;
ED;ag ageg;

Triggers

Triggers

Ejecución de un trigger

Definición de un trigger

Partes de un trigger

Definición formal del trigger

Nuestro primer trigger

Que es lo que queremos?

Variables del sistema

Variables del trigger

Función trigger

Otro trigger

La nombramos con un sufijo tri para darnos cuenta que es una función perteneciente a un
trigger y no confundirla con otra que tengamos en nuestro servidor.

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 15

Definición formal del trigger

Vamos a la definicion formal del trigger:

CREATETR
GGERac aiza_ie

AFTERUDATEie

FREACRW
EXECUTERCEDUREie
_ac aizad_