x4WpHxrb

1create table cliente (
2  idcliente int auto_increment not null primary key,
3  idpersona int unique,
4  idjuridico int unique,
5  ultima_compra date,
6  estado varchar(45),
7  constraint persona_fk foreign key (idpersona) references persona_natural(idpersona),
8  constraint juridico_fk foreign key (idjuridico) references juridico(idjuridico)
9);
10	
11create view vw_cliente as
12select case when p.idpersona is not null then 'PERSONA' ELSE 'JURIDICO' END as tipo_cliente,
13       c.ultima_compra,
14       c.estado,
15       p.*,
16       j.*
17  from cliente c
18  left join persona_natural p
19    on p.idpersona = c.idpersona
20  left join (select j.idjuridico,
21                    j.razon_social,
22                    j.ruc,
23                    j.tipo_entidad,
24                    j.correo_electronico,
25                    p.numero as numero_representante_legal,
26                    p.cedula as cedula_representante_legal,
27                    p.direccion as direccion_representate_legal
28               from juridico j
29               join persona_natural p
30                 on j.idrepresentante_legal = p.idpersona) j
31    on j.idjuridico = c.idjuridico;
32	
33drop view if exists vw_cliente;
34drop table if exists cliente;
35drop table if exists juridico;
36drop table if exists persona_natural;
37drop procedure if exists cliente_check;
38
39create table persona_natural (
40  idpersona int auto_increment not null primary key,
41  numero varchar(45),
42  cedula int,
43  direccion varchar(45)
44);
45
46create table juridico (
47  idjuridico int auto_increment not null primary key,
48  razon_social varchar(45),
49  ruc varchar(45),
50  tipo_entidad varchar(45),
51  correo_electronico varchar(45),
52  idrepresentante_legal int not null,
53  constraint representate_legal_fk foreign key (idrepresentante_legal) references persona_natural(idpersona)
54);
55
56create table cliente (
57  idcliente int auto_increment not null primary key,
58  idpersona int unique,
59  idjuridico int unique,
60  ultima_compra date,
61  estado varchar(45),
62  constraint persona_fk foreign key (idpersona) references persona_natural(idpersona),
63  constraint juridico_fk foreign key (idjuridico) references juridico(idjuridico)
64);
65
66delimiter $$
67create procedure cliente_check(in idpersona int, in idjuridico int)
68begin
69  if (idpersona is null and idjuridico is null) or (idpersona is not null and idjuridico is not null) then
70    signal sqlstate '45000' set message_text = 'Hay que asignar un valor sea a idpersona o idjuridico.';
71  end if;
72end
73$$
74
75create trigger cliente_insert_trigger
76before insert on cliente
77for each row
78begin
79  call cliente_check(new.idpersona, new.idjuridico);
80end
81$$
82
83create trigger cliente_update_trigger
84before update on cliente
85for each row
86begin
87  call cliente_check(new.idpersona, new.idjuridico);
88end
89$$
90delimiter ;
91
92create view vw_cliente as
93select case when p.idpersona is not null then 'PERSONA' ELSE 'JURIDICO' END as tipo_cliente,
94       c.ultima_compra,
95       c.estado,
96       p.*,
97       j.*
98  from cliente c
99  left join persona_natural p
100    on p.idpersona = c.idpersona
101  left join (select j.idjuridico,
102                    j.razon_social,
103                    j.ruc,
104                    j.tipo_entidad,
105                    j.correo_electronico,
106                    p.numero as numero_representante_legal,
107                    p.cedula as cedula_representante_legal,
108                    p.direccion as direccion_representate_legal
109               from juridico j
110               join persona_natural p
111                 on j.idrepresentante_legal = p.idpersona) j
112    on j.idjuridico = c.idjuridico;
113
114insert into persona_natural (numero, cedula, direccion) values ('11223344', 123456789, '100 Main St.');
115insert into cliente (idpersona, ultima_compra, estado) values (last_insert_id(), '2016-11-01', 'ACTIVO');
116insert into persona_natural (numero, cedula, direccion) values ('55667788', 987654321, '200 Legal St.');
117insert into juridico (razon_social, correo_electronico, idrepresentante_legal) values ('Microsoft', 'microsoft@gmail.com', last_insert_id());
118insert into cliente (idjuridico, ultima_compra, estado) values (last_insert_id(), '2016-11-02', 'ACTIVO');
119
120select * from vw_cliente;
121
122drop view if exists vw_cliente;
123drop table cliente;
124drop table juridico;
125drop table persona_natural;
126drop procedure cliente_check;