-Reto 2-
Aplicacion de Triggers After y Before
Enunciado
Se requiere diseñar un sistema de matrícula para una universidad que consta de tres tablas principales: una tabla de estudiantes que almacena información personal y académica de los alumnos, una tabla de materias que enumera todas las asignaturas ofrecidas por la universidad, y una tabla de matrícula que sirve como tabla intermedia, relacionando a los estudiantes con las materias en las que se inscriben, junto con información adicional relevante.
Se crea la base de datos desde cero y se le da el nombre de MatriculaUni la cual contendrá 3 tablas Estudiante,Materia y Matricula(Movimiento o relacional)
Luego se le ingresa algunos datos a la tabla Estudiante dejando en cero los campos donde se realizaran los cálculos implementando los Triggers
Trigger Before
Crea un trigger llamado calvalmateria que calcule el valor de la materia de la tabla materia cuando se ingrese una materia nueva
1.Que se desea consultar o modificar y de que tabla
Materia.valtotal
Materia.valcredito
Materia.cantcreditos
2.Tablas involucradas
Materia
3.Condicion
Ninguna
4.comandos
Before
Set
New
5.Sintaxis de la sentencia sql
set new.valtotal=new.valcredito*new.cantcreditos;
6.Trigger
Se le ingresa registros a la tabla Materia para ver si el trigger funciona correctamente
Trigger After
Ahora necesitamos crear un trigger llamado actualizar_valmatricula que se active despues de ingresar un registro en la tabla Matricula y que sume el valor de cada materia relacionada con cada estudiante y que actualice el valor del campo de la tabla valmatricula con su respectivo valor
Análisis
1.Que se desea consultar o modificar y de que tabla
Estudiante.valmatricula
2.Tablas involucradas
Matricula
Estudante
Materia
3.Condicion
matricula.idestu=estudiante.idestu;
4.comandos
After
Update
Set
Where
inner join
5.Sintaxis de la sentencia sql
update estudiante set valmatricula=(select sum(materia.valtotal) from matricula inner join materia on matricula.codmate=materia.codmate where matricula.idestu=estudiante.idestu);
6.Trigger
Agregamos registros a la tabla matricula para ver el funcionamiento del trigger
Creamos copia de seguridad
Archivo TxT con los comandos empleados
Parte 2 Del reto 2
En esta segunda parte se implementara el uso de los trigger con múltiples acciones y el uso del condicional If.
Para esta segunda parte del reto se agregaron las siguientes tablas
- estadomate
- estadopago
- pagomatricula
- tipopago
- historialpago(Tabla para guardar el historial de los pagos)
Nuevo modelo MER
NUEVO DICCIONARIO DE DATOS
y a la tabla matricula se le agrego una llave foránea que la relaciona con la tabla estadomate que se encarga de mostrar el estado de la materia ya sea activa, pendiente, etc. También la tabla estudiante tuvo una pequeña modificación ya que ya no contiene el valmatricula(valor de la matricula) ahora ese campo pertenece o hace parte de la tabla pagomatricula.
Después de ya tener las tablas creadas con sus registros necesarios (las que lo necesitan) procedemos a crear los siguientes triggers
1-Actualizaremos el trigger que se encarga de calcular el valor de la matricula. Ahora lo que hará es actualizar el valor de la matricula en la tabla pagomatricula. Se emplea una condición If que requiere de un contador el cual se encarga de contra cuantos registros hay de X estudiante en esa tabla. SI el contador no incrementa significa que no hay registros de pago de matricula de ese estudiante entonces lo que hace es insertar un registro con algunos datos predefinidos, envía la fecha del sistema y envía el valor de la primera materia que matricula, con un estado de pago el cual es el '001' que significa pendiente(dato muy importante).
2-En caso de que el estudiante ya tenga un registro en la tabla pagomatricula y su estado de pago es '002'(Pagado) lo que hace es eliminar ese registro y crear otro nuevo.
3-Por lo contrario, si el estado de pago es '001' simplemente actualiza el registro específicamente el valmatricula sumando las materias de la tabla matricula que perteneces al id del estudiante de ese registro.
Analisis de la creacion del trigger
1.Que se desea consultar o modificar y de que tabla
pagomateria.valmatricula
pagomateria.idestu,codtppago,fechahora,codestadopago
2.Tablas involucradas
Matricula
Estudante
Materia
pagomatricula
estadopago
tipopago
estadomate
3.Condicion
SELECT codestadopago FROM pagomatricula WHERE idestu = NEW.idestu) = '002'
pm.idestu = NEW.idestu;
4.comandos
After
Set
Where
Select
Count
Insert into
Delete
Update
Now()
If/Else
5.Sintaxis de la sentencia sql
DECLARE contador INT;
SELECT COUNT(*) INTO contador
FROM pagomatricula AS pm
WHERE pm.idestu = NEW.idestu;
IF contador = 0 THEN
INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago)
VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001');
ELSE
IF (SELECT codestadopago FROM pagomatricula WHERE idestu = NEW.idestu) = '002' THEN
DELETE FROM pagomatricula WHERE idestu = NEW.idestu;
INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago)
VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001');
ELSE
UPDATE pagomatricula
SET valmatricula = valmatricula + (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate),
fechahora = NOW()
WHERE idestu = NEW.idestu;
END IF;
END IF;
6.Trigger
Trigger Procesar pago se activa después de actualizar el código del estado de pago por el código '002' de la tabla pagomatricula donde después de actualizar ese campo lleva la información a otra tabla llamada HistorialPago con los mismos campos. Al mismo tiempo actualiza el código del estado de las materias por '001'(Activo) del estudiante del registro de pagomatricula.
Analisis de la creacion del trigger
1.Que se desea consultar o modificar y de que tabla
pagomateria.valmatricula
pagomateria.idestu,codtppago,fechahora,codestadopago
Historialpago.idestu,codtppago,fechahora,codestadopago
matricula.codestado
2.Tablas involucradas
Matricula
Estudante
Materia
pagomatricula
estadopago
tipopago
estadomate
3.Condicion
OLD.codestadopago != '002' AND NEW.codestadopago = '002'
pm.idestu = NEW.idestu;
4.comandos
After
Set
Where
Select
Count
Insert into
Delete
Update
Now()
If/Else
5.Sintaxis de la sentencia sql
IF OLD.codestadopago != '002' AND NEW.codestadopago = '002' THEN
INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago)
VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago);
UPDATE matricula
SET codestadomate = '001'
WHERE idestu = NEW.idestu;
6.Trigger
