triggers

create trigger insertar_persona2 AFTER INSERT ON persona
FOR EACH ROW
INSERT INTO persona2 (nombre_nuevo, apellido_nuevo, telefono_nuevo, direccion_nuevo, email_nuevo, celular_nuevo, usuario. modificado, proceso, idpersona);


create trigger modifica_persona2 before update on persona
FOR EACH ROW
INSERT INTO persona2 (nombre_anterior, apellido_anterior, telefono_anterior, direccion_anterior, email_anterior, celular_anterior,nombre_nuevo, apellido_nuevo, telefono_nuevo, direccion_nuevo, email_nuevo, celular_nuevo, usuario, modificado,  idpersona)
values (old.nombre, old.apellido,old.telefono,old.direccion, old.email, old.celular, new.nombre, new.apellido,new.telefono, new.direccion, new.email, new.celular, current_user(), now(), new.idpersona);

Procedimiento Abogados

delimiter //
CREATE PROCEDURE audiencia (in idpersona VARCHAR(10))
block1: BEGIN
declare  ultfech date;
declare  cont int (20) ;
declare  idcaso1 varchar (20) ;
declare cursor1 for select audiencia.fecha from audiencia;persona;caso;tipocaso where audiencia.idcaso=caso.idcaso
and persona.idpersona=audiencia.idacusado
and caso.idtipocaso=tipocaso.idtipocaso
and audiencia.fecha>=( DATE_ADD( CURDATE() ,INTERVAL-12 MONTH))
cont=0;
OPEN cursor1;
cursor1: LOOP
FETCH cursor1 Into  ultfech
if ( ultfech >=( DATE_ADD( CURDATE() ,INTERVAL-12 MONTH))) then
cont=cont+1;
END IF
END LOOP cursor1;
if (cont=0)then
UPDATE caso SET idestadocaso="1" WHERE  idcaso=idcaso;
END IF
close  audiencia;

//

Base de Datos Abogados

DROP DATABASE IF EXISTS acta;

CREATE DATABASE caso;

USE  caso;

DROP TABLE IF EXISTS persona;
CREATE TABLE persona(idpersona varchar(10) not null,
nombres varchar(100) not null, apellidos varchar(100) not null,
telefono varchar(15) not null, direccion varchar(100) not null,
email varchar(100), celular varchar(20),
PRIMARY KEY(idpersona));

DROP TABLE IF EXISTS rol;
CREATE TABLE rol (idrol varchar(2) not null,
descripcion varchar(100) not null,
PRIMARY KEY(idrol));

DROP TABLE IF EXISTS tipocaso;
CREATE TABLE tipocaso (idtipocaso varchar(2) not null,
descripcion varchar(100) not null,
PRIMARY KEY(idtipocaso));

DROP TABLE IF EXISTS fallo;
CREATE TABLE fallo (idfallo varchar(2) not null,
descripcion varchar(100) not null,
PRIMARY KEY(idfallo));

DROP TABLE IF EXISTS caso;
CREATE TABLE caso (idcaso varchar(5) not null,
descripcion varchar(200) not null,
idtipocaso varchar(2) not null,
idfallo varchar(2) not null,
PRIMARY KEY(idcaso),
FOREIGN KEY(idtipocaso) REFERENCES tipocaso(idtipocaso),
FOREIGN KEY(idfallo) REFERENCES fallo(idfallo));

DROP TABLE IF EXISTS sede;
CREATE TABLE sede (idsede varchar(2) not null,
nombre varchar(100) not null,
direccion varchar(100) not null,
PRIMARY KEY(idsede));

DROP TABLE IF EXISTS personarolcaso;
CREATE TABLE personarolcaso (idpersona varchar(10) not null,
idrol varchar(2) not null,
idcaso varchar(5) not null,
FOREIGN KEY(idpersona) REFERENCES persona(idpersona),
FOREIGN KEY(idrol) REFERENCES rol(idrol),
FOREIGN KEY(idcaso) REFERENCES caso(idcaso));

DROP TABLE IF EXISTS audiencia;
CREATE TABLE audiencia (idaudiencia varchar(2) not null,
fecha date not null,
idcaso varchar(5) not null,
idsede varchar(2) not null,
idjuez varchar(10) not null,
idabogadodefensor varchar(10) not null,
idabogadoacusador varchar(10) not null,
idfiscal varchar(10) not null,
idacusado varchar(10) not null,
PRIMARY KEY(idaudiencia),
FOREIGN KEY(idjuez) REFERENCES persona(idpersona),
FOREIGN KEY(idabogadodefensor) REFERENCES persona(idpersona),
FOREIGN KEY(idfiscal) REFERENCES persona(idpersona),
FOREIGN KEY(idacusado) REFERENCES persona(idpersona),
FOREIGN KEY(idsede) REFERENCES sede(idsede));

DROP TABLE IF EXISTS testigoaudiencia;
CREATE TABLE testigoaudiencia (idaudiencia varchar(2) not null,
idpersona varchar(10) not null,
FOREIGN KEY(idaudiencia) REFERENCES audiencia(idaudiencia),
FOREIGN KEY(idpersona) REFERENCES persona(idpersona));

INSERT INTO persona

VALUES ('71000000','SARA','GARCIA','1111111','CARRERA 1 CALLE 100','sara@garcia.com','1111111111'),

('72000000','LAURA','AGUDELO','2222222','CARRERA 2 CALLE 200','laura@agudelo.com','2222222222'),

('73000000','ANA','MONTOYA','3333333','CARRERA 3 CALLE 300','ana@montoya.com','3333333333'),

('74000000','HAROL','VALDES','4444444','CARRERA 4 CALLE 400','harol@valdes.com','4444444444'),

('75000000','SAMUEL','GARCIA','5555555','CARRERA 5 CALLE 500','samuel@garcia.com','5555555555'),

('76000000','EDWIN','ZAPATA','6666666','DESCONOCIDO','','6666666666'),

('77000000','DARIO','VALENCIA','7777777','DESCONOCIDO','','7777777777'),

('78000000','PEDRO','IDARRAGA','8888888','DESCONOCIDO','','8888888888'),

('79000000','ANIBAL','MAYA','9999999','DESCONOCIDO','','9999999999'),

('80000000','VANESA','MONSALVE','8000000','CARRERA 30 EDIFICIO 50','vanesa@medellin.co','8000000000'),

('81000000','MABEL','COLORADO','8111111','CARRERA 35 EDIFICIO 55','mabel@medellin.co','8111111111'),

('82000000','HARRY','MONSALVE','8222222','CARRERA 45 EDIFICIO 65','harry@medellin.co','8222222222'),

('83000000','TOMY','MONSALVE','8333333','CARRERA 35 EDIFICIO 55','tomy@medellin.co','8333333333'),

('84000000','LEONEL','LOAIZA','8444444','BELLAVISTA AV. 56','','8444444444');

INSERT INTO rol

VALUES ('01','JUEZ'),('02','FISCAL'),('03','ABOGADO DEFENSOR'),('04','ABOGADO ACUSADOR'),

('05','ACUSADO'),('06','TESTIGO'),('07','JURADO'),('08','SECRETARIO');

INSERT INTO tipocaso

VALUES ('01','HOMICIDIO'),('02','HURTO CALIFICADO'),('03','LESIONES PERSONALES'),

('04','VIOLACION'),('05','PREVARICATO'),('06','FALSEDAD EN DOCUMENTO PUBLICO'),

('07','EVASION DE IMPUESTOS'),('08','DEMANDA POR ALIMENTOS');

INSERT INTO fallo

VALUES ('01','EN PROCESO'),('02','FALLADO'),('03','SUSPENDIDO'),('04','EN RECESO'),

('05','CANCELADO');

INSERT INTO caso

VALUES ('C0001','ASESINATO PERSONA MAYOR EN LA CRA 50 CALLE 50 EN MEDELLIN','01','01'),

('C0002','EVASION DE IMPUESTOS POR PARTE DEL CIUDADANO REY','07','01'),

('C0003','VIOLACION DEL MENOR DE EDAD XXX POR PARTE DEL CIUDADANO X','04','02'),

('C0004','FALTA A LAS OBLIGACIONES PARENTALES POR PARTE DEL SEÑOR ABC RESPECTO DE SU HIJO','08','04'),

('C0005','ROBO A MANO ARMADA DEL VEHICULO XYZ POR PARTE DEL MENOR DE EDAD AAA','02','05'),

('C0006','LESIONES PERSONALES CON ARMA BLANCA','03','01');

INSERT INTO sede

VALUES ('S1','EDIFICIO DEL CAFE','CARRERA 46 CALLE 49'),('S2','EDFICIO FURATENA','CALLE 50 CARRERA 47'),

('S3','EDIFICIO COLTEJER','CARRERA 46 CALLE 52'),('S4','OFICINA REGIONAL MEDELLIN','CARRERA 25 CALLE 80'),

('S5','OFICINA POBLADO','CALLE 10 CARRERA 39');

INSERT INTO personarolcaso

VALUES ('71000000','01','C0002'),('72000000','02','C0002'),('71000000','03','C0001'),

('73000000','04','C0005'),('72000000','04','C0005'),('75000000','01','C0003'),

('75000000','01','C0004'),('75000000','03','C0002'),('74000000','04','C0001'),

('74000000','02','C0004'),('74000000','04','C0002'),('76000000','05','C0002'),

('71000000','03','C0003'),('72000000','04','C0003'),('73000000','02','C0003'),

('77000000','05','C0003'),('71000000','03','C0004'),('73000000','04','C0004'),

('78000000','05','C0004'),('72000000','01','C0001'),('73000000','02','C0001'),

('79000000','05','C0001'),('80000000','02','C0006'),('81000000','03','C0006'),

('82000000','06','C0006'),('83000000','01','C0006'),('84000000','05','C0006'),

('79000000','04','C0006');

INSERT INTO audiencia

VALUES ('01','2012-07-4','C0002','S5','71000000','75000000','74000000','72000000','76000000'),

('02','2012-06-15','C0003','S2','75000000','71000000','72000000','73000000','77000000'),

('03','2012-05-19','C0004','S3','75000000','71000000','73000000','74000000','78000000'),

('04','2012-04-25','C0001','S1','72000000','71000000','74000000','73000000','79000000'),

('05','2012-03-06','C0006','S4','83000000','81000000','79000000','80000000','84000000');

INSERT INTO testigoaudiencia 

VALUES ('01','71000000'),('01','75000000'),('01','74000000'),('01','72000000'),('01','76000000'),

('02','75000000'),('02','71000000'),('02','72000000'),('02','73000000'),('02','77000000'),

('03','75000000'),('03','71000000'),('03','73000000'),('03','74000000'),('03','78000000'),

('04','72000000'),('04','71000000'),('04','74000000'),('04','73000000'),('04','79000000'),

('05','83000000'),('05','81000000'),('05','79000000'),('04','80000000'),('04','84000000');