|
Creaci�n de triggers.
Ya
hemos visto los aspectos mas importantes para la utilizaci�n b�sica
de PostgreSQL, como lo es la creaci�n de tablas, el llenado de estas
mismas, consulta de de datos a estas tablas, as� como tambi�n la
eliminaci�n de tablas o actualizaci�n de alg�n registro en
particular y lo mas importante las relaciones que pueden haber entre
estas (tablas).
Con lo anterior,
ahora procederemos a dar un ejemplo mas o menos complejo en un
principio, pero que resulta f�cil de entender si se sabe un poco de
programaci�n o SQL, nos referimos a los TRIGGERS y las FUNCIONES
(disparadores), explicaremos un poco de lo que se trata.
PostgreSQL
tiene
algunas interfaces cliente como Perl, Tcl, Python y C, as� como dos
Lenguajes Procedurales (PL). Tambi�n es posible llamar a
funciones C como acciones trigger.
Un trigger es un
fragmento de c�digo que uno le dice a PostgreSQL que corra antes de
o despu�s de que una tabla sea modificada. Un trigger tiene el poder
de:
-
Asegurar que
una columna contiene valores predefinidos
-
Asegurar que
una fila de auditoria sea insertada en otra tabla
-
Luego de
encontrar que la nueva informaci�n es inconsistente con otras
cosas en la base de datos, levanta un error que causar� que toda
la transacci�n se aborte
La sintaxis
para la creaci�n de triggers es la siguiente:
CREATE TRIGGER <trigger name> <BEFORE|AFTER> <INSERT|DELETE|UPDATE>
ON <relation name> FOR EACH <ROW|STATEMENT>
EXECUTE PROCEDURE <procedure name> (<function args>);
-
El
nombre del trigger se usar� si se desea eliminar el trigger. Se
usa como argumento del comando DROP TRIGGER.
-
La
palabra siguiente determina si la funci�n debe ser llamada antes (BEFORE)
o despu�s (AFTER) del evento.
-
El
siguiente elemento del comando determina en que evento/s ser�
llamada la funci�n. Es posible especificar m�ltiples eventos
utilizado el operador OR.
-
El
nombre de la relaci�n (relation name) determinar� la tabla
afectada por el evento.
-
La
instrucci�n FOR EACH determina si el trigger se ejecutar� para
cada fila afectada o bien antes (o despu�s) de que la secuencia se
haya completado.
-
El
nombre del procedimiento (procedure name) es la funci�n llamada.
Los triggers
se pueden escribir en cualquiera de los siguientes lenguajes:
C, PL/pgSQL y PL/Tcl.
Nosotros usaremos el leguaje PL/pgSQL, el cual lo
crearemos con un c�digo que mostraremos mas adelante.
Ejemplo de una
Funci�n con un Trigger.
A continuaci�n se presenta un ejemplo de un Procedimiento, que
calcula el promedio de notas (calificaciones) de ciertos alumnos
ingresados en la base de datos, estos alumnos tienen una cantidad de
ramos (cursos) especificas, o sea que algunos alumnos tendr�n 6
ramos, otros 5 ramos, etc.
Creaci�n de tablas:
Primero verificamos si las tablas existen, si es as�, las
eliminamos.
drop table alumnos;
drop table cursos;
drop table alumno_curso;
drop table notas;
drop table promedio;
Ahora creamos la tabla alumnos, la cual tiene como clave primaria el
id_alumno.
create table alumnos (
id_alumno integer NOT NULL,
nombre varchar(40) NOT NULL,
direccion varchar(80) NOT NULL,
ciudad varchar(30) NOT NULL,
constraint PK_id_alumno PRIMARY KEY(id_alumno));
Ahora creamos la tabla cursos, la cual tiene como clave primaria el
id_curso.
create table cursos (
id_curso integer NOT NULL,
nombre_curso varchar(50) NOT NULL,
constraint PK_id_curso PRIMARY KEY(id_curso));
Ahora creamos la tabla alumno_curso en la que se mostraran los
cursos que tienen los distintos alumnos. Cabe se�alar que algunos
alumnos tienen todos los ramos y otros no los tienen todos.
La clave primaria es id_alumno_curso, la cual se forma por las
claves for�neas id_alumno e id_curso las que se referencian por sus
respectivas tablas.
create table alumno_curso (
id_alumno decimal(5,0) NOT NULL,
id_curso decimal(5,0) NOT NULL,
constraint PK_id_alumno_curso PRIMARY KEY(id_alumno,id_curso),
constraint FK_id_alumno FOREIGN KEY(id_alumno) REFERENCES
alumnos(id_alumno),
constraint FK_id_curso FOREIGN KEY(id_curso) REFERENCES
cursos(id_curso));
Ahora creamos la tabla notas, la cual tiene como clave primaria
promedio, la que se forma por los atributos id_alumno e id_curso las
que se referencian por sus respectivas tablas.
Todos los alumnos deben tener las notas puestas. El atributo
promedio no debe llenarse, ya que este se colocara autom�ticamente a
trav�s de un procedimiento. El promedio que se colocar� en la tupla
ser� solamente el promedio de un ramo que el alumno tiene. Para
calcular el promedio de otro ramo, se debe poner el mismo id del
alumno (id_alumno) y el otro id del curso (id_curso) al cual
queremos calcularle el promedio. Recordemos que todos los alumnos no
tienen la misma cantidad de ramos.
CREATE TABLE notas (
id_alumno integer NOT NULL,
id_curso integer NOT NUll,
nota1 integer NOT NULL,
nota2 integer NOT NULL,
nota3 integer NOT NULL,
nota4 integer NOT NULL,
promedio integer NULL,
CONSTRAINT pk_promedio PRIMARY KEY (id_alumno,id_curso),
CONSTRAINT fk_id_alumno FOREIGN KEY (id_alumno)
references alumnos(id_alumno),
CONSTRAINT fk_id_curso FOREIGN KEY (id_curso)
references cursos(id_curso));
Por ultimo se crea la tabla promedio, en donde no existe clave
primaria ya que si ponemos una clave primaria, postgresql nos
obligara a ingresar datos a esta.
En esta tabla se registraran los promedios finales de los alumnos a
partir de la cantidad de ramos que este tiene.
create table promedio(
alumno varchar(20),
promedio_final integer);
Cargar el Lenguaje:
Esta es la funci�n que se utiliza para cargar el lenguaje PL/PGSQL y
as� poder ejecutar el procedimiento y el trigger que hemos creado.
CREATE FUNCTION
plpgsql_call_handler() RETURNS OPAQUE AS
'/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'pl/pgsql';
Creaci�n del procedimiento:
El procedimiento presentado a continuaci�n, calcula el promedio de
los alumnos a partir de sus notas. A continuaci�n se explicara como
funciona.
Primero se comprueba si la funci�n existe, se es as�, la eliminamos
y adem�s eliminamos en forma de cascada todo lo que dependa de ella
.
DROP FUNCTION promedio() CASCADE;
Ahora creamos la funci�n promedio(), la cual retornara un trigger,
que es declarado mas abajo.
Se declaran tres variables:
r: que es de tipo record, la cual nos sirve para tomar los
datos de una tupla o registro en una tabla.
p: de tipo flotante, para guardar el promedio calculado de un
alumno en particular una vez insertada sus notas, este ser�
insertado en la tabla promedio.
n: de tipo entero, la cual nos sirve para tener la cantidad
de promedios de un alumno en particular
Cada vez que se calcula uno, va incrementando cada vez que un
promedio es calculado.
El promedio que se calcular� (NEW.promedio), saldr� de las notas que
insertemos (NEW.nota_n). Donde n es una de las notas, desde la 1 a
la 4.
raise notice, es para imprimir por pantalla, es este caso en
la terminal, el promedio del alumno al que se le insertaron las
notas.
Luego con select into, se selecciona las tuplas en donde el id del
alumno debe ser igual al id del alumno insertado.
Se cuentan sus promedios y se suman, luego esta suma de los promedio
se dividen por n, con la cual obtenemos el promedio final del
alumno. Cada vez que se calcula un promedio este se suma a la suma (sum(promedio))
y se vuelve dividir por n (numero de promedios).
Despu�s se imprime por pantalla el promedio final del alumno con sus
respectivo id.
Por ultimo insertamos en la tabla promedio los valores id del alumno
y el promedio final calculado hasta ese entonces, porque recordemos
que si insertamos las notas de otro ramo, el promedio va a sufrir
cambios.
Ahora indicamos que el lenguaje usado es PL/PGSQL, el cual ya
habiamos creado.
CREATE FUNCTION promedio() RETURNS trigger AS'
declare
r record;
p float;
n integer;
BEGIN
NEW.promedio := (NEW.nota1 + NEW.nota2 + NEW.nota3 + NEW.nota4) / 4;
raise notice '' % promedio : %'', new.id_alumno, new.promedio;
select into r sum(promedio) as suma, count(*) as num
from notas
where id_alumno = new.id_alumno;
n := r.num + 1;
p := (r.suma + new.promedio) / n;
raise notice '' % promedio : % %'', new.id_alumno, n, p;
INSERT INTO promedio values (NEW.id_alumno,p);
RETURN NEW;
END;
'LANGUAGE 'plpgsql';
Ahora creamos el trigger llamado promedio2, el cual se ejecutara
entes de insertar en la tabla notas, en donde para cada tupla
ejecutara la funci�n promedio antes explicada.
CREATE TRIGGER promedio2 BEFORE insert ON notas
FOR EACH ROW EXECUTE PROCEDURE promedio();
Ingreso de datos a las tablas
Inserta en la tabla cursos.
insert into cursos values (112,'MATEMATICAS');
insert into cursos values (122,'BASE DE DATOS');
insert into cursos values (123,'REDES');
insert into cursos values (124,'PROGRAMACION');
insert into cursos values (125,'SISTEMAS DE INFORMACION');
insert into cursos values (126,'ELECTRICIDAD');
Inserta en la tabla alumnos.
insert into alumnos values (1,'LUIS VEGA','1 NORTE 123','TALCA');
insert into alumnos values (2,'JULIO LOBO','2 SUR 1232','SAN
JAVIER');
insert into alumnos values (3,'PAULO NUNEZ','AVDA. LOS HUASOS','GRANEROS');
insert into alumnos values (4,'JUAN ORTEGA','3 ORIENTE
54','PARRAL');
insert into alumnos values (5,'CRISTIAN MENA','AVDA. LAS
ROSAS','TALCA');
insert into alumnos values (6,'ROBERTO CASTRO','LA
FLORIDA','TALCA');
Inserta en la tabla alumno_curso los alumnos con codigo n.
Alumno 1, tiene todos los ramos.
insert into alumno_curso values (1,112);
insert into alumno_curso values (1,122);
insert into alumno_curso values (1,123);
insert into alumno_curso values (1,124);
insert into alumno_curso values (1,125);
insert into alumno_curso values (1,126);
Alumno 2, tiene todo los ramos.
insert into alumno_curso values (2,112);
insert into alumno_curso values (2,122);
insert into alumno_curso values (2,123);
insert into alumno_curso values (2,124);
insert into alumno_curso values (2,125);
insert into alumno_curso values (2,126);
Alumno 3, tiene todos los ramos.
insert into alumno_curso values (3,112);
insert into alumno_curso values (3,122);
insert into alumno_curso values (3,123);
insert into alumno_curso values (3,124);
insert into alumno_curso values (3,125);
insert into alumno_curso values (3,126);
Alumno 4, no tiene REDES y ELECTRICIDAD.
insert into alumno_curso values (4,112);
insert into alumno_curso values (4,122);
insert into alumno_curso values (4,124);
insert into alumno_curso values (4,125);
Alumno 5, no tiene SISTEMAS DE INFORMACI�N.
insert into alumno_curso values (5,112);
insert into alumno_curso values (5,122);
insert into alumno_curso values (5,123);
insert into alumno_curso values (5,124);
insert into alumno_curso values (5,126);
Alumno 6, no tiene MATEM�TICAS y ELECTRICIDAD.
insert into alumno_curso values (6,122);
insert into alumno_curso values (6,123);
insert into alumno_curso values (6,124);
insert into alumno_curso values (6,125);
Inserta las notas del alumno 1 en cada ramo en la tabla notas. El
promedio se calcula a trav�s de un procedimiento y el promedio final
se insertar� en la tabla promedio a trav�s de este mismo
procedimiento.
insert into notas values (1,112,45,45,65,43);
insert into notas values (1,122,56,61,45,43);
insert into notas values (1,123,67,32,21,54);
insert into notas values (1,124,70,49,35,65);
insert into notas values (1,125,21,45,59,65);
Inserta las notas del alumno 2.
insert into notas values (2,112,50,50,50,50);
insert into notas values (2,122,70,70,70,70);
insert into notas values (2,123,10,10,10,10);
insert into notas values (2,124,20,20,20,20);
insert into notas values (2,125,60,60,60,60);
insert into notas values (2,126,30,30,30,30);
Inserta las notas del alumno 3.
insert into notas values (3,112,50,50,50,50);
insert into notas values (3,122,70,70,70,70);
insert into notas values (3,123,10,10,10,10);
insert into notas values (3,124,20,20,20,20);
insert into notas values (3,125,60,60,60,60);
insert into notas values (3,126,30,30,30,30);
Inserta las notas del alumno 4.
insert into notas values (4,112,10,10,11,70);
insert into notas values (4,122,20,20,20,70);
insert into notas values (4,124,67,10,20,70);
insert into notas values (4,125,64,60,60,70);
Inserta las notas del alumno 5.
insert into notas values (5,112,10,10,10,30);
insert into notas values (5,122,20,10,10,65);
insert into notas values (5,123,30,50,10,50);
insert into notas values (5,124,40,10,10,10);
insert into notas values (5,126,50,10,10,10);
Inserta las notas del alumno 6.
insert into notas values (6,122,60,60,60,60);
insert into notas values (6,123,10,20,30,40);
insert into notas values (6,124,30,30,30,30);
insert into notas values (6,125,61,62,63,64);
Aqui mostramos algunas im�genes de
consulta que se hicieron a las tablas:
Con esto concluye la
explicaci�n de nuestro TRIGGER, a lo mejor un poco complicado en un
principio, pero que si observa con atenci�n se podr� entender bien,
ahora, quiz�s faltaron algunas especificaciones m�nimas, lo cual
nosotros lo tomamos por sabido ya que la mayor�a de los alumnos
hicimos el curso de base de datos el semestre pasado.
|
Atr�s | Inicio |
Siguiente | |
