ejemplo en sql server(triggers, cursores)

 1) modelado de base de datos




2) script: crear las tablas

create database BDMatricula;
use BDMatricula;
go

create table alumno(
idAlumno int primary key identity(1,1),
nombres varchar(50) not null,
apellidos varchar(50) not null
)

create table curso(
idCurso int primary key identity(1,1),
nombre varchar(50) not null,
creditos smallint not null default(3),
)

create table preRequisito(
idCurso int references curso(idCurso),
curso int references curso(idCurso)
)
create table seccion(
idSeccion int primary key identity(1,1),
nombre varchar(50) not null,
)
create table horario(
idSeccion int references seccion(idSeccion),
idCurso int references curso(idCurso),
vacantes int not null default(0),
dia varchar(10) not null,
horaInico int not null,
horaFinal int not null
)
create table nota(
idCurso int references curso(idCurso),
idAlumno int references alumno(idAlumno),
nota1 int  default(0),
nota2 int  default(0),
nota3 int  default(0),
nota4 int  default(0)
)

create table cronograma(
idCronograma int primary key identity(1,1),
inicioMatricula date not null default getdate(),
finMatricula date not null
)

create table matricula(
idMatricula int primary key identity(1,1),
idAlumno int references alumno(idAlumno),
fecha date default getdate()
)
create table detalleMatricula(
idMatricula int references matricula(idMatricula),
idCurso int references curso(idCurso),
idSeccion int references seccion(idSeccion)
)
create table registrarMatricula(
idMatricula int references matricula(idMatricula),
idCronograma int references cronograma(idCronograma),
recibo varchar(50) not null,
totalCreditos int not null
constraint creditos check( totalCreditos>=12 and totalCreditos<=22)
)

3)trigger para detalle matrícula

create trigger tgDetalleMatricula on detalleMatricula for insert, update
as
begin
 declare @idAlumno int
 declare @idSeccion int
 declare @idMatricula int
 declare @idCurso int
 select
  @idMatricula=i.idMatricula,
  @idCurso=i.idCurso,
  @idSeccion=i.idSeccion,
  @idAlumno=m.idAlumno
 from inserted i
 inner join matricula m on i.idMatricula=m.idMatricula
 -- b.4 Debe haber vacante en la sección donde desea matricularse
 declare @vacantes int
 select
  @vacantes=vacantes
 from horario
 where idSeccion=@idSeccion and idCurso=@idCurso
 if @vacantes=0
 begin
  raiserror ('no hay vacantes disponibles', 16, 2)
     rollback transaction 
 end
end
go

4) trigger para registrar matrícula

create trigger tgRegistraMatricula on registrarMatricula
for insert, update
as
begin
 declare @idMatricula int
 declare @idAlumno int
 declare @idCronograma int

 select
  @idMatricula=idMatricula,
  @idCronograma=idCronograma
 from inserted
 select @idAlumno=idAlumno
 from matricula
 -- a.1El registro de la matrícula solamente se puede efectuar
 -- en las fechas establecidas en el cronograma;
 declare @inicioMatricula date
 declare @finMatricula date
 declare @fechaMatricula date
 select
  @inicioMatricula=inicioMatricula,
  @finMatricula=finMatricula
 from cronograma
 where idCronograma=@idCronograma;
 select
  @fechaMatricula=fecha
 from matricula
 where idMatricula=@idMatricula;
 
 if @fechaMatricula<@inicioMatricula or @fechaMatricula>@finMatricula
 begin
  raiserror ('las matrícula se realizó fuera de fecha', 16, 2)
     rollback transaction
 end
 -- a.2 se debe verificar el pago de matrícula registrando
 -- el número de recibo respectivo
 -- solución: registrarMatricula.recibo es not null por lo tanto
 -- si o si tiene que ingresarse el campo recibo

 -- b.1 La nota del alumno, en el curso pre requisito
 --  del Plan de estudios actual, debe estar aprobado
 declare @nota1 int,@nota2 int,@nota3 int, @nota4 int
 declare @desaprobado int
 set @desaprobado=0
 declare infoNotasPreRequisitos cursor read_only for
 select
  n.nota1,
  n.nota2,
  n.nota3,
  n.nota4
 from matricula m
 join alumno a on m.idAlumno=m.idAlumno
 join detalleMatricula dm on dm.idMatricula=m.idMatricula
 join curso c on dm.idCurso=c.idCurso
 join preRequisito pr on pr.idCurso=c.idCurso
 join nota n on n.idCurso=pr.curso
 where m.idMatricula=@idMatricula;
 open infoNotas
 FETCH NEXT FROM infoNotasPreRequisitos INTO @nota1,@nota2,@nota3,@nota4
 WHILE @@fetch_status = 0
 begin
  if @nota1<=10 and @nota2<=10 and @nota3<=10 and @nota4<=10
   set @desaprobado=1
  FETCH NEXT FROM infoNotasPreRequisitos INTO @nota1,@nota2,@nota3,@nota4
 end
 close infoNotasPreRequisitos
 if @desaprobado=1
 begin
  raiserror ('cursos pre requisitos no aprobados', 16, 2)
     rollback transaction  
 end

 --  El total de créditos no debe ser menor de 12, ni mayor a 22 créditos.
 declare @totalCreditos int
 declare @creditos int
 set @totalCreditos=0
 declare cTotalCreditos cursor read_only for
 select c.creditos
 from matricula m
 inner join detalleMatricula dm on dm.idMatricula=m.idMatricula
 inner join curso c on dm.idCurso=c.idCurso
 where m.idMatricula=1
 open cTotalCreditos
 FETCH NEXT FROM cTotalCreditos  INTO @creditos
 WHILE @@fetch_status = 0
 BEGIN
  set @totalCreditos=@totalCreditos+@creditos
  FETCH NEXT FROM cTotalCreditos  INTO @creditos
 END
 close cTotalCreditos
 if @totalCreditos<12 or @totalCreditos>22
 begin
  raiserror ('total de créditos no válidos', 16, 2)
     rollback transaction 
 end
-- b.3 El alumno no puede tener una asignatura con más de 3 notas desaprobadas
 set @desaprobado=0
 declare notasAlumno cursor read_only for
 select
 nota1,nota2,nota3,nota4
 from nota
 where idAlumno=@idAlumno
 open notasAlumno
 FETCH NEXT FROM notasAlumno  INTO @nota1,@nota2,@nota3,@nota4
 WHILE @@fetch_status = 0
 BEGIN
  if @nota1<=10 and @nota2<=10 and @nota3<=10 and @nota4<=10
   set @desaprobado=1
  FETCH NEXT FROM notasAlumno  INTO @nota1,@nota2,@nota3,@nota4
 END
 close notasAlumno
 if @desaprobado=1
 begin
  raiserror ('Más de tres notas desaprobatorias', 16, 2)
     rollback transaction 
 end 

 --c1. Considerar que al confirmarse el registro de la matricula del alumno;
 --se actualizará la vacante del curso
 
 
 declare cambiarVacantes cursor read_only for
 select
  idCurso,
  idSeccion
 from detalleMatricula dm
 where dm.idMatricula=@idMatricula
 open cambairVacantes
  FETCH NEXT FROM cambairVacantes  INTO @idCurso,@idSeccion
 WHILE @@fetch_status = 0
 BEGIN
  update horario
  set vacantes=vacantes-1
  where idCurso=@idCurso and idSeccion=@idSeccion
  FETCH NEXT FROM cambairVacantes  INTO @idCurso,@idSeccion
 END
 close cambairVacantes

end

Compartir

elson