· 9 years ago · Oct 23, 2016, 09:12 PM
1--TRABAJO PRACTICO BASE DE DATOS
2
3--RESTRICCIONES
4-- INCISO A
5-- 1) SQL ESTANDAR
6 CREATE DOMAIN Dom_rolcliente
7 AS CHAR(1) CHECK ( value IN ( 'C' , 'E' ) )
8
9-- 2) PostgreSQL: idem SQL estandar (ALTERNATIVO)
10 ALTER TABLE GR41_PERSONA
11 ADD CONSTRAINT CHK_rol_cliente CHECK (rol IN( 'C' , 'E' ))
12
13-- 3) sentencia de activacion de la restriccion
14 INSERT INTO GR41_persona values (1,'d',36546456,'Carlos','Mangazzo','1993/07/03',NULL,'1234',TRUE,223,3464,'m','carlos@mail','sarasa',465,'lazio','D',15);
15 UPDATE GR41_persona set rol='R' where rol='C';
16
17-- INCISO B
18-- 1) SQL ESTANDAR
19 CREATE DOMAIN DOM_cien
20 AS DATE NOT NULL CHECK (DATE_PART('year', CURRENT_DATE) - DATE_PART('year', value) < 100)
21
22-- 2) PostgreSQL
23 ALTER TABLE GR41_PERSONA
24 ADD CONSTRAINT CHK_cien CHECK (DATE_PART('year', CURRENT_DATE) - DATE_PART('year', fecha_nacimiento) < 100)
25
26-- 3) sentencia de activacion de la restriccion
27 INSERT INTO GR41_persona values (2,'d',36546456,'Carlos','Mangazzo','1893/07/03',NULL,'1234',TRUE,223,3464,'m','carlos@mail','sarasa',465,'lazio','D',15);;
28 UPDATE GR41_persona set fecha_nacimiento='1893/07/03' where id_persona=2;
29
30-- INCISO C
31-- 1) SQL ESTANDAR
32 ALTER TABLE GR41_PERSONA
33 ADD CONSTRAINT CHK_inactivo CHECK (( activo = TRUE ) OR ( fecha_baja IS NOT NULL ))
34
35-- 2) PostgreSQL
36 idem SQL estandar
37
38-- 3) sentencia de activacion de la restriccion
39 INSERT INTO GR41_persona values (34,'d',36546456,'Carlos','Mangazzo','1993/07/03',NULL,'1234',FALSE,223,3464,'m','carlos@mail','sarasa',465,'lazio','D',15);
40 UPDATE GR41_persona SET activo=FALSE WHERE id_persona=1;
41
42-- INCISO D (TOTALMENTE HECHO)
43-- 1) SQL ESTANDAR
44 no puede resolverse declarativamente pese a que sea una restriccion de tupla, se resuelve con un trigger
45
46-- 2)
47 CREATE FUNCTION FN_GR41_modBaja()
48 RETURNS TRIGGER AS $TR_GR41_modBaja$
49 BEGIN
50 IF ( old.activo = false AND new.activo = true) THEN
51 new.activo = true;
52 new.fecha_baja = NULL;
53 ELSIF ( old.activo = false ) THEN
54 RAISE EXCEPTION 'No se puede modificar datos de una persona dada de baja';
55 END IF;
56 RETURN new;
57 END;
58 $TR_GR41_modBaja$ LANGUAGE plpgsql;
59
60 CREATE TRIGGER TR_GR41_modBaja
61 BEFORE UPDATE ON GR41_persona
62 FOR EACH ROW EXECUTE PROCEDURE FN_GR41_modBaja();
63
64 3)
65 INSERT INTO GR41_persona values (8,'d',3,'asd','asdf','1993/07/03',' 2001/07/03','fsdgf',TRUE,223,346,'m','dfgdfg','kfjdsf',465,'cuatro','D',15)
66 UPDATE GR41_persona set activo = false where id_persona = 8
67 UPDATE GR41_persona set mail = 'sss' where id_persona = 8 --Escribe error por pantalla
68 UPDATE GR41_persona set activo = true where id_persona = 8 --La persona puede resusitar
69
70
71-- INCISO E (TOTALMENTE HECHO)
72-- 1) SQL ESTANDAR
73 CREATE ASSERTION ASS_maxlineas
74 CHECK ( NOT EXISTS ( SELECT 1
75 FROM comprobante_conl c
76 JOIN linea_comprobante l ON ( c.id_tcomp = l.id_tcomp AND c.id_comp = l.id_comp )
77 GROUP BY c.id_comp , c.id_tcomp
78 HAVING COUNT (*) > 10
79 ) );
80
81-- 2)
82 CREATE FUNCTION FN_GR41_maxComp()
83 RETURNS TRIGGER AS $$
84 BEGIN
85 IF EXISTS
86 (
87 SELECT COUNT (*)
88 FROM GR41_linea_comprobante
89 GROUP BY id_tcomp, id_comp
90 HAVING COUNT (*) > 10
91 )
92 THEN RAISE EXCEPTION 'No puede haber comprobantes con mas de 10 lineas';
93 END IF;
94 RETURN NEW;
95 END; $$ LANGUAGE plpgsql;
96
97
98 CREATE TRIGGER TR_GR41_linea_comprobante_maxl
99 BEFORE INSERT ON GR41_linea_comprobante
100 FOR EACH ROW EXECUTE PROCEDURE FN_GR41_maxComp();
101
102
103 CREATE FUNCTION FN_GR41_noActLinea()
104 RETURNS TRIGGER AS $$
105 BEGIN
106 RAISE EXCEPTION 'No se pueden actualizar las lineas de comprobante';
107 END; $$ LANGUAGE plpgsql;
108
109 CREATE TRIGGER TR_GR41_linea_comprobante_noAct
110 AFTER UPDATE ON GR41_linea_comprobante
111 FOR EACH ROW EXECUTE PROCEDURE FN_GR41_noActLinea();
112
113 3)
114
115 INSERT INTO gr41_persona values (1,'d',36546456,'Carlos','Mangazzo','1993/07/03','2001/07/03','1234',TRUE,223,3464,'m','carlos@mail','sarasa',465,'lazio','C',15);
116 INSERT into gr41_cliente values(1,10000,4);
117 INSERT into gr41_tipo_comprobante values(1,'Factura');
118 INSERT into gr41_comprobante values(1,2,'2008/08/4','Comprobante para probar trigger','Bueno','2008/12/4',5000,'F');
119 INSERT into gr41_comprobante_conl values(1,2,1);
120 INSERT into gr41_linea_comprobante values(1,'asd',3,434,1,2);
121 INSERT into gr41_linea_comprobante values(2,'aasdsd',2,543,1,2);
122 INSERT into gr41_linea_comprobante values(3,'asdd',4,4664,1,2);
123 INSERT into gr41_linea_comprobante values(4,'gasd',35,567,1,2);
124 INSERT into gr41_linea_comprobante values(5,'hasd',1,865,1,2);
125 INSERT into gr41_linea_comprobante values(6,'jasd',23,789,1,2);
126 INSERT into gr41_linea_comprobante values(7,'jeasd',34,213,1,2);
127 INSERT into gr41_linea_comprobante values(8,'yasd',5,645,1,2);
128 INSERT into gr41_linea_comprobante values(9,'uasd',76,6434,1,2);
129 INSERT into gr41_linea_comprobante values(10,'qasd',7,6745,1,2);
130 INSERT into gr41_linea_comprobante values(11,'agsd',8,346,1,2);
131
132-- INCISO F
133-- 1) SQL ESTANDAR
134 CREATE ASSERTION ASS_importe
135 CHECK ( NOT EXISTS ( SELECT *
136 FROM GR41_comprobante_conl cc
137 JOIN GR41_comprobante c ON ( c.id_comp = cc.id_comp AND c.id_tcomp = cc.id_tcomp )
138 JOIN GR41_linea_comprobante l ON ( cc.id_comp = l.id_comp AND cc.id_tcomp = l.id_tcomp )
139 GROUP
140 HAVING c.importe <> SUM(l.importe)
141 ))
142
143-- 2)
144
145 drop trigger TR_GR41_comprobante_ImporteComprobante on GR41_comprobante;
146 drop trigger TR_GR41_linea_comprobante_ImporteLineaComprobante on GR41_linea_comprobante;
147 drop function FN_GR41_importeComprobante();
148 drop function FN_ImporteLineaComprobante();
149
150 CREATE FUNCTION FN_GR41_importeComprobante()
151 RETURNS TRIGGER AS $$
152 BEGIN
153 IF EXISTS
154 (
155 SELECT SUM(l.importe)
156 FROM GR41_comprobante_conl cc
157 JOIN GR41_comprobante c ON ( c.id_comp = cc.id_comp AND c.id_tcomp = cc.id_tcomp )
158 JOIN GR41_linea_comprobante l ON ( cc.id_comp = l.id_comp AND cc.id_tcomp = l.id_tcomp )
159 group by c.importe
160 having c.importe <> SUM(l.importe)
161 )
162 THEN RAISE EXCEPTION 'El importe no coincide con la suma de las lineas';
163 END IF;
164 RETURN NEW;
165 END; $$ LANGUAGE plpgsql;
166
167 CREATE TRIGGER TR_GR41_comprobante_ImporteComprobante
168 AFTER UPDATE ON GR41_comprobante
169 FOR EACH ROW
170 WHEN NOT (OLD.comentario = )
171 -- WHEN NOT (OLD.importe IS DISTINCT FROM NEW.importe)
172 EXECUTE PROCEDURE FN_GR41_importeComprobante();
173
174 CREATE FUNCTION FN_ImporteLineaComprobante()
175 RETURNS TRIGGER AS $$
176 DECLARE
177 v_total INTEGER;
178 BEGIN
179 SELECT SUM(l.importe)
180 into v_total
181 FROM GR41_linea_comprobante l
182 where id_comp = new.id_comp and id_tcomp = new.id_tcomp;
183 update GR41_comprobante set importe = v_total where id_comp = new.id_comp and id_tcomp = new.id_tcomp;
184 return new;
185 END; $$ LANGUAGE plpgsql;
186
187 CREATE TRIGGER TR_GR41_linea_comprobante_ImporteLineaComprobante
188 AFTER UPDATE OR INSERT ON GR41_linea_comprobante
189 FOR EACH ROW EXECUTE PROCEDURE FN_ImporteLineaComprobante();
190
191-- 3)
192 select c.id_comp , c.id_tcomp , c.importe , sum(l.importe) from gr41_comprobante c
193 join gr41_comprobante_conl cl on ( cl.id_comp = c.id_comp and cl.id_tcomp = c.id_tcomp )
194 join gr41_linea_comprobante l on ( l.id_comp = c.id_comp and l.id_tcomp = c.id_tcomp )
195 group by c.id_comp,c.id_tcomp
196
197 INSERT INTO gr41_persona values (2,'D',36546456,'Pepe','Soriano','1985/07/03','20015/08/15','1234',TRUE,223,5555,'m','pepesoriano@gmail.com','monte',678,'cuatro','E',7000);
198 INSERT into gr41_cliente values(2,20000,654);
199 INSERT into gr41_tipo_comprobante values(1,'Factura');
200 INSERT into gr41_comprobante values(1,4,'2008/08/4','Comprobante para probar trigger','Bueno','2008/12/4',5000,'F');
201 INSERT into gr41_comprobante_conl values(1,4,2);
202 INSERT into gr41_linea_comprobante values(1,'asd',3,2000,1,4);
203 INSERT into gr41_linea_comprobante values(2,'aasdsd',2,1000,1,4);
204 INSERT into gr41_linea_comprobante values(3,'asdd',4,1001,1,4);
205
206 INSERT into gr41_comprobante values(1,8,'2008/08/4','Comprobante para probar trigger','Bueno','2008/12/4',4006,'F');
207 INSERT into gr41_comprobante_conl values(1,8,2);
208 INSERT into gr41_linea_comprobante values(1,'akd',3,500,1,8);
209 INSERT into gr41_linea_comprobante values(2,'jhd',2,500,1,8);
210 INSERT into gr41_linea_comprobante values(3,'afd',4,550,1,8);
211
212
213
214 --------------SERVICIO 1---------------------
215
216 CREATE FUNCTION TRFN_GR41_actualizarSaldoInsert()
217 RETURNS TRIGGER AS $$
218 DECLARE importeAux decimal(18,2);
219 BEGIN
220 SELECT c.importe
221 INTO importeAux
222 FROM GR41_comprobante c
223 WHERE NEW.id_comp = c.id_comp and NEW.id_tcomp = c.id_tcomp;
224
225 IF (NEW.id_tcomp = '1') THEN
226 UPDATE GR41_cliente C set saldo = (saldo + importeAux) where (C.id_persona = NEW.id_persona);
227 ELSIF (NEW.id_tcomp = '2') THEN
228 UPDATE GR41_CLIENTE C SET SALDO = (saldo - importeAux) where (C.id_persona = NEW.id_persona);
229 END IF;
230 RETURN NEW;
231 END; $$ LANGUAGE plpgsql;
232
233 CREATE TRIGGER TR_GR41_comprobante_comprobanteActualizarSaldoInsert
234 AFTER INSERT ON GR41_comprobante_conl
235 FOR EACH ROW EXECUTE PROCEDURE TRFN_GR41_act
236
237
238 CREATE FUNCTION TRFN_GR41_actualizarSaldoInsertSinL()
239 RETURNS TRIGGER AS $$
240 DECLARE importeAux decimal(18,2);
241 BEGIN
242 SELECT c.importe
243 INTO importeAux
244 FROM GR41_comprobante c
245 WHERE NEW.id_comp = c.id_comp and NEW.id_tcomp = c.id_tcomp;
246
247 IF (NEW.id_tcomp = '1') THEN
248 UPDATE GR41_cliente C set saldo = (saldo + importeAux) where (C.id_persona = NEW.id_persona);
249 ELSIF (NEW.id_tcomp = '2') THEN
250 UPDATE GR41_CLIENTE C SET SALDO = (saldo - importeAux) where (C.id_persona = NEW.id_persona);
251 END IF;
252 RETURN NEW;
253 END; $$ LANGUAGE plpgsql;
254
255 CREATE TRIGGER TR_GR41_comprobante_comprobanteActualizarSaldoInsertSinL
256 AFTER INSERT ON GR41_comprobante_sinl_turno
257 FOR EACH ROW EXECUTE PROCEDURE TRFN_GR41_actualizarSaldoInsertSinL()
258
259 ---------------------------------------------------------------------------
260
261 CREATE FUNCTION TRFN_GR41_ActualizarSaldoUpdate()
262 RETURNS TRIGGER AS $$
263 DECLARE idAux int;
264 BEGIN
265 SELECT cl.id_persona
266 INTO idAux
267 FROM GR41_comprobante_conl cl
268 WHERE NEW.id_comp = cl.id_comp and NEW.id_tcomp = cl.id_tcomp;
269
270 if (NEW.id_tcomp = '1') THEN
271 UPDATE GR41_cliente C set saldo = ((saldo - OLD.importe) + NEW.importe) where (C.id_persona = idAux);
272 ELSIF (NEW.id_tcomp = '2') THEN
273 UPDATE GR41_CLIENTE C SET SALDO = ((saldo + OLD.importe) - NEW.importe) where (C.id_persona = idAux);
274 END IF;
275 RETURN NEW;
276 END; $$ LANGUAGE plpgsql;
277
278 CREATE TRIGGER TR_GR41_comprobante_comprobanteActualizarSaldoUpdate
279 AFTER UPDATE ON GR41_comprobante
280 FOR EACH ROW
281 WHEN (OLD.importe IS DISTINCT FROM NEW.importe)
282 EXECUTE PROCEDURE TRFN_GR41_ActualizarSaldoUpdate()
283
284
285 INSERT INTO GR41_COMPROBANTE VALUES (1,3,NULL,'assa',NULL,NULL,1204,'f')
286 INSERT INTO GR41_COMPROBANTE_CONL VALUES (1,1,1)
287
288
289
290---------------------------------SERVICIO 2--------------------------------------------------------------------------------------------------
291
292 CREATE FUNCTION FN_GR41_generaFact()
293 RETURNS RECORD AS $$
294 DECLARE consulta RECORD;
295 maximo BIGINT;
296 BEGIN
297 SELECT max(id_comp)
298 into maximo
299 from gr41_comprobante;
300 FOR consulta IN ( SELECT s.id_servicio , e.id_equipo , c.id_persona , s.costo
301 FROM gr41_servicio s
302 JOIN gr41_equipo e ON ( s.id_servicio = e.id_servicio )
303 JOIN gr41_cliente c ON ( e.id_persona = c.id_persona )
304 WHERE ( s.periodico = true ) and ( s.activo = true ) )
305 LOOP
306 maximo := maximo +1;
307 INSERT into gr41_comprobante values(1,maximo,current_date,'','Deudor',current_date+30,consulta.costo,'F');
308 END LOOP;
309 RETURN NULL;
310 END; $$ LANGUAGE plpgsql;
311
312 --la funcion se activara con la extension pg_cron, EXPLICAR en informe
313
314
315
316
317 --Tuplas para insertar y probar
318
319 INSERT INTO gr41_persona values(31,'D',38444333,'Carlos','Espinoza','1980/10/02',null,null,true,223,8888,'F','ce@hotmail.com','Avellaneda',501,'cero','C',7000);
320 INSERT INTO gr41_persona values(32,'D',38444332,'Brenda','Gomez','1970/07/24',null,null,true,294,4888,'F','bg@hotmail.com','Brasil',1233,'uno','C',7000);
321
322 INSERT INTO gr41_cliente values(31,10000,31000);
323 INSERT INTO gr41_cliente values(32,20000,32000);
324
325 INSERT INTO gr41_servicio values(1,'servicio 1',true,500.1,4,'mes',true,'A');
326 INSERT INTO gr41_servicio values(2,'servicio 2',false,1000,5,'mes',true,'A');
327 INSERT INTO gr41_servicio values(3,'servicio 3',true,300.23,6,'bimestre',false,'B');
328 INSERT INTO gr41_servicio values(4,'servicio 4',true,5040.1,4,'mes',true,'A');
329 INSERT INTO gr41_servicio values(5,'servicio 5',true,120.1,4,'mes',true,'A');
330
331 INSERT INTO gr41_direccion values(31,'Avellaneda',501,null,null,'Casa',1,7000);
332 INSERT INTO gr41_direccion values(32,'Brasil',501,1,'D','Departamento',2,7000);
333
334 INSERT INTO gr41_equipo values(1,'Equipo 1','00:00:00:00',null,null,1,31,31,'ASUS',2015,'PPPOE','IP FIJA');
335 INSERT INTO gr41_equipo values(2,'Equipo 2','00:00:00:01',null,null,2,31,31,'ACER',2014,'PPTP','DHCP');
336 INSERT INTO gr41_equipo values(3,'Equipo 3','00:00:00:02',null,null,3,32,32,'TOSHIBA',2016,'PPPOE','DHCP');
337 INSERT INTO gr41_equipo values(4,'Equipo 4','00:00:00:03',null,null,4,32,32,'sdfdhj',2016,'PPPOE','DHCP');
338 INSERT INTO gr41_equipo values(5,'Equipo 5','00:00:00:04',null,null,07 7,32,32,'sdf',2016,'PPPOE','DHCP');
339
340
341---------------------------------SERVICIO 3--------------------------------------------------------------------------------------------------
342
343 CREATE FUNCTION TRFN_GR41_ConsistenciaRolCliente()
344 RETURNS TRIGGER AS $$
345 BEGIN
346 IF ( EXISTS (select id_persona from gr41_persona where (id_persona = NEW.id_persona) AND (rol='E')))
347 THEN RAISE EXCEPTION 'La persona posee rol de empleado, no puede ser insertado como cliente';
348 END IF;
349 RETURN NULL;
350 END; $$ LANGUAGE plpgsql;
351
352 CREATE TRIGGER TR_GR41_cliente_ConsistenciaRolCliente
353 AFTER INSERT OR UPDATE ON GR41_cliente
354 FOR EACH ROW EXECUTE PROCEDURE TRFN_GR41_ConsistenciaRolCliente();
355
356 CREATE FUNCTION TRFN_GR41_ConsistenciaRolClienteMod()
357 RETURNS TRIGGER AS $$
358 BEGIN
359 IF (OLD.id_persona <> NEW.id_persona)
360 THEN RAISE EXCEPTION 'No se permite realizar un cambio de id';
361 END IF;
362 RETURN NULL;
363 END; $$ language plpgsql;
364
365 CREATE TRIGGER TR_GR41_Cliente_ConsistenciaRolClienteMod
366 AFTER UPDATE ON GR41_Cliente
367 FOR EACH ROW EXECUTE PROCEDURE TRFN_GR41_ConsistenciaRolClienteMod();
368
369 ---------------------------------------------------------------------------
370
371 CREATE FUNCTION TRFN_GR41_ConsistenciaRolEmpleado()
372 RETURNS TRIGGER AS $$
373 BEGIN
374 IF ( EXISTS (select id_persona from gr41_persona where (id_persona = NEW.id_persona) AND (rol='C')))
375 THEN RAISE EXCEPTION 'La persona posee rol de cliente, no puede ser insertado como empleado';
376 END IF;
377 RETURN NULL;
378 END; $$ language plpgsql;
379
380 CREATE TRIGGER TR_GR41_Empleado_ConsistenciaRolEmpleado
381 AFTER INSERT ON GR41_Empleado
382 FOR EACH ROW EXECUTE PROCEDURE TRFN_GR41_ConsistenciaRolEmpleado();
383
384 CREATE FUNCTION TRFN_GR41_ConsistenciaRolEmpleadoMod()
385 RETURNS TRIGGER AS $$
386 BEGIN
387 IF (OLD.id_persona <> NEW.id_persona)
388 THEN RAISE EXCEPTION 'No se permite realizar un cambio de id';
389 END IF;
390 RETURN NULL;
391 END; $$ language plpgsql;
392
393 CREATE TRIGGER TR_GR41_Empleado_ConsistenciaRolEmpleadoMod
394 AFTER UPDATE ON GR41_Empleado
395 FOR EACH ROW EXECUTE PROCEDURE TRFN_GR41_ConsistenciaRolEmpleadoMod();
396
397---------------------------------VISTA 1--------------------------------------------------------------------------------------------------
398
399 CREATE VIEW VISTA_COMPROBANTES_CL
400 AS SELECT C.id_persona, C.saldo, C.cuit, O.id_tcomp,
401 O.id_comp, O.fecha, O.comentario, O.estado, O.fecha_vencimiento,
402 O.importe, O.tipo_comprobante, E.nro_linea, E.descripcion, E.cantidad --ESTE CHOCLO ES PARA Q NO SE REPITAN DATOS (ID_TCOMP E ID_COMP) CORRESPONDIENTE
403 FROM GR41_CLIENTE C
404 NATURAL JOIN GR41_COMPROBANTE_CONL L
405 NATURAL JOIN GR41_COMPROBANTE O
406 NATURAL JOIN GR41_LINEA_COMPROBANTE E;
407
408 --UNIR LAS 3 TABLAS
409------------------------------VISTA 2--------------------------------------------------------------------------------------------------
410
411 CREATE VIEW GR41_VISTA_CLIENTES AS
412 SELECT *
413 FROM gr41_cliente
414 NATURAL JOIN gr41_persona;
415
416 CREATE VIEW GR41_VISTA_EMPLEADOS AS
417 SELECT *
418 FROM gr41_empleado
419 NATURAL JOIN gr41_persona;
420
421------------------------------VISTA 3--------------------------------------------------------------------------------------------------
422
423 CREATE VIEW GR41_VISTA_CLIENTE_SALDO_DEUDOR AS
424 SELECT *
425 FROM gr41_persona
426 WHERE id_persona in ( SELECT c.id_persona
427 FROM gr41_cliente c
428 WHERE ( c.saldo > 0 ))