· 9 years ago · Oct 26, 2016, 08:00 PM
1--TRABAJO PRACTICO BASE DE DATOS
2
3--RESTRICCIONES
4 --
5 CREATE SEQUENCE SEC_idfactura MINVALUE 1;
6 CREATE SEQUENCE SEC_idremito MINVALUE 1;
7 CREATE SEQUENCE SEC_idrecibo MINVALUE 1;
8
9-- INCISO A
10-- 1) SQL ESTANDAR
11 -- CREATE DOMAIN Dom_rolcliente
12 -- AS CHAR(1) CHECK ( value IN ( 'C' , 'E' ) )
13
14-- 2) PostgreSQL: idem SQL estandar (ALTERNATIVO)
15 ALTER TABLE GR41_PERSONA
16 ADD CONSTRAINT CHK_rol_cliente CHECK (rol IN( 'C' , 'E' ));
17
18-- 3) sentencia de activacion de la restriccion
19/*
20 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);
21 UPDATE GR41_persona set rol='R' where rol='C';
22*/
23
24-- INCISO B
25-- 1) SQL ESTANDAR
26 -- CREATE DOMAIN DOM_cien
27 -- AS DATE NOT NULL CHECK (DATE_PART('year', CURRENT_DATE) - DATE_PART('year', value) < 100)
28
29-- 2) PostgreSQL
30 ALTER TABLE GR41_PERSONA
31 ADD CONSTRAINT CHK_cien CHECK (DATE_PART('year', CURRENT_DATE) - DATE_PART('year', fecha_nacimiento) < 100);
32
33-- 3) sentencia de activacion de la restriccion
34/*
35 INSERT INTO GR41_persona values (2,'d',36546456,'Carlos','Mangazzo','1893/07/03',NULL,'1234',TRUE,223,3464,'m','carlos@mail','sarasa',465,'lazio','C',15);
36 UPDATE GR41_persona set fecha_nacimiento='1893/07/03' where id_persona=2;
37*/
38
39-- INCISO C
40-- 1) SQL ESTANDAR
41 ALTER TABLE GR41_PERSONA
42 ADD CONSTRAINT CHK_inactivo CHECK ( NOT( (activo = false) AND (fecha_baja IS NULL) ) );
43
44-- 2) PostgreSQL: idem SQL estandar
45
46-- 3) sentencia de activacion de la restriccion
47 --CASO 1
48 -- INSERT INTO GR41_persona values (34,'d',36546456,'Carlos','Mangazzo','1993/07/03',NULL,'1234',FALSE,223,3464,'m','carlos@mail','sarasa',465,'lazio','C',15);
49
50 --CASO 2
51 -- INSERT INTO GR41_persona values (34,'d',36546456,'Carlos','Mangazzo','1993/07/03',NULL,'1234',TRUE,223,3464,'m','carlos@mail','sarasa',465,'lazio','C',15);
52 -- UPDATE GR41_persona SET activo=FALSE WHERE id_persona=34;
53
54-- INCISO D (TOTALMENTE HECHO)
55-- 1) SQL ESTANDAR
56 -- no puede resolverse declarativamente pese a que sea una restriccion de tupla, se resuelve con un trigger
57
58-- 2)
59 CREATE FUNCTION TRFN_GR41_modBaja()
60 RETURNS TRIGGER AS $TR_GR41_modBaja$
61 BEGIN
62 IF ( old.activo = false AND new.activo = true) THEN
63 new.activo = true;
64 new.fecha_baja = NULL;
65 ELSIF ( old.activo = false ) THEN
66 RAISE EXCEPTION 'No se pueden modificar datos de una persona dada de baja';
67 END IF;
68 RETURN new;
69 END;
70 $TR_GR41_modBaja$ LANGUAGE plpgsql;
71
72 CREATE TRIGGER TR_GR41_modBaja
73 BEFORE UPDATE ON GR41_persona
74 FOR EACH ROW EXECUTE PROCEDURE TRFN_GR41_modBaja();
75
76-- 3)
77/* 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','C',15)
78 UPDATE GR41_persona set activo = false where id_persona = 8
79 UPDATE GR41_persona set mail = 'sss' where id_persona = 8 --Escribe error por pantalla
80
81
82 UPDATE GR41_persona set activo = true where id_persona = 8 --La persona puede resusitar*/
83
84
85-- INCISO E (TOTALMENTE HECHO)
86-- 1) SQL ESTANDAR
87 /* CREATE ASSERTION ASS_GR41_maxlineas
88 CHECK ( NOT EXISTS ( SELECT 1
89 FROM GR41_comprobante_conl c
90 JOIN GR41_linea_comprobante l ON ( c.id_tcomp = l.id_tcomp AND c.id_comp = l.id_comp )
91 GROUP BY c.id_comp , c.id_tcomp
92 HAVING COUNT (*) > 10
93 ) );*/
94
95-- 2)
96 CREATE FUNCTION TRFN_GR41_maxComp()
97 RETURNS TRIGGER AS $$
98 BEGIN
99 IF EXISTS
100 (
101 SELECT COUNT (*)
102 FROM GR41_linea_comprobante
103 GROUP BY id_tcomp, id_comp
104 HAVING COUNT (*) > 10
105 )
106 THEN RAISE EXCEPTION 'No puede haber comprobantes con mas de 10 lineas';
107 END IF;
108 RETURN NEW;
109 END; $$ LANGUAGE plpgsql;
110
111
112 CREATE TRIGGER TR_GR41_linea_comprobante_maxl
113 AFTER INSERT ON GR41_linea_comprobante
114 FOR EACH ROW EXECUTE PROCEDURE TRFN_GR41_maxComp();
115
116
117 CREATE FUNCTION TRFN_GR41_noActLinea()
118 RETURNS TRIGGER AS $$
119 BEGIN
120 RAISE EXCEPTION 'No se pueden actualizar las lineas de comprobante';
121 END; $$ LANGUAGE plpgsql;
122
123 CREATE TRIGGER TR_GR41_linea_comprobante_noAct
124 AFTER UPDATE ON GR41_linea_comprobante
125 FOR EACH ROW EXECUTE PROCEDURE TRFN_GR41_noActLinea();
126
127-- 3)
128/*
129 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);
130 INSERT INTO gr41_tipo_comprobante values (1,'factura');
131 INSERT into gr41_cliente values(1,10000,4);
132 INSERT into gr41_comprobante values(1,1,'2008/08/4','Comprobante para probar trigger','Bueno','2008/12/4',5000,'F');
133 INSERT into gr41_comprobante_conl values(1,1,1);
134 INSERT into gr41_linea_comprobante values(1,'asd',3,434,1,1);
135 INSERT into gr41_linea_comprobante values(2,'ssd',2,543,1,1);
136 INSERT into gr41_linea_comprobante values(3,'sdd',4,4664,1,1);
137 INSERT into gr41_linea_comprobante values(4,'gad',35,567,1,1);
138 INSERT into gr41_linea_comprobante values(5,'hsd',1,865,1,1);
139 INSERT into gr41_linea_comprobante values(6,'jsd',23,789,1,1);
140 INSERT into gr41_linea_comprobante values(7,'jjd',34,213,1,1);
141 INSERT into gr41_linea_comprobante values(8,'ysd',5,645,1,1);
142 INSERT into gr41_linea_comprobante values(9,'usd',7,6434,1,1);
143 INSERT into gr41_linea_comprobante values(10,'qd',7,6745,1,1);
144 INSERT into gr41_linea_comprobante values(11,'ad',8,346,1,1);
145*/
146
147-- INCISO F
148-- 1) SQL ESTANDAR
149 /* CREATE ASSERTION ASS_importe
150 CHECK ( NOT EXISTS ( SELECT *
151 FROM GR41_comprobante_conl cc
152 JOIN GR41_comprobante c ON ( c.id_comp = cc.id_comp AND c.id_tcomp = cc.id_tcomp )
153 JOIN GR41_linea_comprobante l ON ( cc.id_comp = l.id_comp AND cc.id_tcomp = l.id_tcomp )
154 GROUP BY c.importe
155 HAVING c.importe <> SUM(l.importe)
156 ));
157 */
158-- 2)
159 CREATE FUNCTION TRFN_GR41_importeComprobante()
160 RETURNS TRIGGER AS $$
161 BEGIN
162 IF ( OLD.importe <> NEW.importe ) THEN
163 IF EXISTS
164 (
165 SELECT SUM(l.importe)
166 FROM GR41_comprobante_conl cc
167 JOIN GR41_comprobante c ON ( c.id_comp = cc.id_comp AND c.id_tcomp = cc.id_tcomp )
168 JOIN GR41_linea_comprobante l ON ( cc.id_comp = l.id_comp AND cc.id_tcomp = l.id_tcomp )
169 GROUP BY c.importe
170 having c.importe <> SUM(l.importe)
171 )
172 THEN RAISE EXCEPTION 'El importe no coincide con la suma de las lineas';
173 END IF;
174 ELSE RAISE EXCEPTION 'No se puede modificar el comprobante';
175 END IF;
176 RETURN NEW;
177 END; $$ LANGUAGE plpgsql;
178
179 CREATE TRIGGER TR_GR41_comprobante_ImporteComprobante
180 AFTER UPDATE ON GR41_comprobante
181 FOR EACH ROW EXECUTE PROCEDURE TRFN_GR41_importeComprobante();
182
183 CREATE FUNCTION TRFN_GR41_ImporteLineaComprobante()
184 RETURNS TRIGGER AS $$
185 DECLARE
186 v_total INTEGER;
187 BEGIN
188 SELECT SUM(l.importe)
189 into v_total
190 FROM GR41_linea_comprobante l
191 where id_comp = new.id_comp and id_tcomp = new.id_tcomp;
192 update GR41_comprobante set importe = v_total where id_comp = new.id_comp and id_tcomp = new.id_tcomp;
193 return new;
194 END; $$ LANGUAGE plpgsql;
195
196 CREATE TRIGGER TR_GR41_linea_comprobante_ImporteLineaComprobante
197 AFTER UPDATE OR INSERT ON GR41_linea_comprobante
198 FOR EACH ROW EXECUTE PROCEDURE TRFN_GR41_ImporteLineaComprobante();
199
200-- 3)
201/*
202 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','C',7000);
203 INSERT into gr41_cliente values(2,20000,0);
204 INSERT into gr41_tipo_comprobante values(1,'Factura');
205 INSERT into gr41_comprobante values(1,1,'2008/08/4','Comprobante para probar trigger','Bueno','2008/12/4',5000,'F');
206 INSERT into gr41_comprobante_conl values(1,1,2);
207 INSERT into gr41_linea_comprobante values(1,'asd',3,2000,1,1);
208 INSERT into gr41_linea_comprobante values(2,'aasdsd',2,1000,1,1);
209 INSERT into gr41_linea_comprobante values(3,'asdd',4,1001,1,1);
210*/
211
212
213 --------------SERVICIO 1---------------------
214
215 CREATE FUNCTION TRFN_GR41_actualizarSaldoInsert()
216 RETURNS TRIGGER AS $$
217 DECLARE importeAux decimal(18,2);
218 BEGIN
219 SELECT c.importe
220 INTO importeAux
221 FROM GR41_comprobante c
222 WHERE NEW.id_comp = c.id_comp and NEW.id_tcomp = c.id_tcomp;
223
224 IF (NEW.id_tcomp = '1') THEN
225 UPDATE GR41_cliente C set saldo = (saldo + importeAux) where (C.id_persona = NEW.id_persona);
226 ELSIF (NEW.id_tcomp = '2') THEN
227 UPDATE GR41_CLIENTE C SET SALDO = (saldo - importeAux) where (C.id_persona = NEW.id_persona);
228 END IF;
229 RETURN NEW;
230 END; $$ LANGUAGE plpgsql;
231
232 CREATE TRIGGER TR_GR41_comprobante_comprobanteActualizarSaldoInsert
233 AFTER INSERT ON GR41_comprobante_conl
234 FOR EACH ROW EXECUTE PROCEDURE TRFN_GR41_actualizarSaldoInsert();
235
236
237 CREATE FUNCTION TRFN_GR41_actualizarSaldoInsertSinL()
238 RETURNS TRIGGER AS $$
239 DECLARE importeAux decimal(18,2);
240 BEGIN
241 SELECT c.importe
242 INTO importeAux
243 FROM GR41_comprobante c
244 WHERE NEW.id_comp = c.id_comp and NEW.id_tcomp = c.id_tcomp;
245
246 IF (NEW.id_tcomp = '1') THEN
247 UPDATE GR41_cliente C set saldo = (saldo + importeAux) where (C.id_persona = NEW.id_persona);
248 ELSIF (NEW.id_tcomp = '2') THEN
249 UPDATE GR41_CLIENTE C SET SALDO = (saldo - importeAux) where (C.id_persona = NEW.id_persona);
250 END IF;
251 RETURN NEW;
252 END; $$ LANGUAGE plpgsql;
253
254 CREATE TRIGGER TR_GR41_comprobante_comprobanteActualizarSaldoInsertSinL
255 AFTER INSERT ON GR41_comprobante_sinl_turno
256 FOR EACH ROW EXECUTE PROCEDURE TRFN_GR41_actualizarSaldoInsertSinL();
257
258 ---------------------------------------------------------------------------
259
260 CREATE FUNCTION TRFN_GR41_ActualizarSaldoUpdate()
261 RETURNS TRIGGER AS $$
262 DECLARE idAux int;
263 BEGIN
264 SELECT cl.id_persona
265 INTO idAux
266 FROM GR41_comprobante_conl cl
267 WHERE NEW.id_comp = cl.id_comp and NEW.id_tcomp = cl.id_tcomp;
268
269 IF (NEW.id_tcomp = '1') THEN
270 UPDATE GR41_cliente C set saldo = ((saldo - OLD.importe) + NEW.importe) where (C.id_persona = idAux);
271 ELSIF (NEW.id_tcomp = '2') THEN
272 UPDATE GR41_CLIENTE C SET SALDO = ((saldo + OLD.importe) - NEW.importe) where (C.id_persona = idAux);
273 END IF;
274 RETURN NEW;
275 END; $$ LANGUAGE plpgsql;
276
277 CREATE TRIGGER TR_GR41_comprobante_comprobanteActualizarSaldoUpdate
278 AFTER UPDATE ON GR41_comprobante
279 FOR EACH ROW
280 WHEN (OLD.importe IS DISTINCT FROM NEW.importe)
281 EXECUTE PROCEDURE TRFN_GR41_ActualizarSaldoUpdate();
282
283-- Tuplas para probar
284/*
285 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','C',7000);
286 INSERT INTO gr41_persona values (1,'D',36546456,'Pepe','Soriano','1985/07/03','20015/08/15','1234',TRUE,223,5555,'m','pepesoriano@gmail.com','monte',678,'cuatro','E',7000);
287 INSERT into gr41_cliente values(2,0,329);
288 INSERT into gr41_empleado values(1,'03/07/2005');
289 INSERT into gr41_tipo_comprobante values(1,'Factura');
290 INSERT into gr41_tipo_comprobante values(2,'recibo');
291 INSERT into gr41_comprobante values(1,1,'2008/08/4','Comprobante para probar trigger','Bueno','2008/12/4',5000,'F');
292 INSERT into gr41_comprobante values(1,2,'2008/08/4','Comprobante para probar trigger','Bueno','2008/12/4',10000,'F');
293 INSERT into gr41_comprobante values(2,1,'2008/08/4','Comprobante para probar trigger','Bueno','2008/12/4',20000,'R');
294 INSERT into gr41_comprobante_conl values(1,1,2);
295 INSERT into gr41_comprobante_conl values(1,2,2);
296 INSERT into gr41_turno values(1,'03/07/2005',NULL,500,NULL,1,1);
297 INSERT into gr41_comprobante_sinl_turno values(2,1,2,1);
298*/
299
300
301---------------------------------SERVICIO 2--------------------------------------------------------------------------------------------------
302
303 CREATE FUNCTION FN_GR41_generaFact()
304 RETURNS RECORD AS $$
305 DECLARE consulta RECORD;
306 aux BIGINT;
307 BEGIN
308 ALTER TABLE GR41_comprobante DISABLE TRIGGER TR_GR41_comprobante_ImporteComprobante;
309 FOR consulta IN ( SELECT s.id_servicio , e.id_equipo , c.id_persona , s.costo
310 FROM gr41_servicio s
311 JOIN gr41_equipo e ON ( s.id_servicio = e.id_servicio )
312 JOIN gr41_cliente c ON ( e.id_persona = c.id_persona )
313 WHERE ( s.periodico = true ) and ( s.activo = true ) )
314 LOOP
315 INSERT INTO gr41_comprobante values(1,0,current_date,'','Deudor',current_date+30,consulta.costo,'F');
316 SELECT last_value INTO aux
317 FROM sec_idfactura;
318 INSERT INTO gr41_comprobante_conl values(1,aux,consulta.id_persona);
319 INSERT INTO gr41_linea_comprobante values(1,'Servicios pendientes de pago',1,consulta.costo,1,aux);
320 END LOOP;
321 ALTER TABLE GR41_comprobante ENABLE TRIGGER TR_GR41_comprobante_ImporteComprobante;
322 RETURN NULL;
323 END; $$ LANGUAGE plpgsql;
324
325 CREATE FUNCTION FN_GR41_facturaRemitos()
326 RETURNS RECORD AS $$
327 DECLARE remitos RECORD;
328 aux BIGINT;
329 BEGIN
330 ALTER TABLE GR41_comprobante DISABLE TRIGGER TR_GR41_comprobante_ImporteComprobante;
331 FOR remitos IN ( SELECT cc.importe , c.id_persona
332 FROM gr41_cliente c
333 NATURAL JOIN gr41_comprobante_conl cl
334 NATURAL JOIN gr41_comprobante cc
335 WHERE (cc.id_tcomp = 3) AND ( EXTRACT(MONTH FROM (current_date - interval '1 month')) = EXTRACT(MONTH FROM cc.fecha)) )
336 LOOP
337 INSERT INTO gr41_comprobante values(1,0,current_date,'','FacturaRemitos',current_date+30,remitos.importe,'F');
338 SELECT last_value INTO aux
339 FROM sec_idfactura;
340 INSERT INTO gr41_comprobante_conl values(1,aux,remitos.id_persona);
341 INSERT INTO gr41_linea_comprobante values(1,'Remitos no pagados',1,remitos.importe,1,aux);
342 END LOOP;
343 ALTER TABLE GR41_comprobante ENABLE TRIGGER TR_GR41_comprobante_ImporteComprobante;
344 RETURN NULL;
345 END; $$ LANGUAGE plpgsql;
346 --Tuplas para insertar y probar
347/*
348 INSERT INTO gr41_tipo_comprobante values(1,'Factura');
349 INSERT INTO gr41_tipo_comprobante values(2,'Recibo');
350 INSERT INTO gr41_tipo_comprobante values(3,'Remito');
351
352
353 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);
354 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);
355
356 INSERT INTO gr41_cliente values(31,10000,31000);
357 INSERT INTO gr41_cliente values(32,20000,32000);
358
359 INSERT INTO gr41_servicio values(1,'servicio 1',true,500.1,4,'mes',true,'A');
360 INSERT INTO gr41_servicio values(2,'servicio 2',false,1000,5,'mes',true,'A');
361 INSERT INTO gr41_servicio values(3,'servicio 3',true,300.23,6,'bimestre',false,'B');
362 INSERT INTO gr41_servicio values(4,'servicio 4',true,5040.1,4,'mes',true,'A');
363 INSERT INTO gr41_servicio values(5,'servicio 5',true,120.1,4,'mes',true,'A');
364
365 INSERT INTO gr41_direccion values(31,'Avellaneda',501,null,null,'Casa',1,7000);
366 INSERT INTO gr41_direccion values(32,'Brasil',501,1,'D','Departamento',2,7000);
367
368 INSERT INTO gr41_equipo values(1,'Equipo 1','00:00:00:00',null,null,1,31,31,'ASUS',2015,'PPPOE','IP FIJA');
369 INSERT INTO gr41_equipo values(2,'Equipo 2','00:00:00:01',null,null,2,31,31,'ACER',2014,'PPTP','DHCP');
370 INSERT INTO gr41_equipo values(3,'Equipo 3','00:00:00:02',null,null,3,32,32,'TOSHIBA',2016,'PPPOE','DHCP');
371 INSERT INTO gr41_equipo values(4,'Equipo 4','00:00:00:03',null,null,4,32,32,'sdfdhj',2016,'PPPOE','DHCP');
372 INSERT INTO gr41_equipo values(5,'Equipo 5','00:00:00:04',null,null,5,32,32,'sdf',2016,'PPPOE','DHCP');
373
374 --Para probar la funcion de los remitos
375
376 INSERT INTO gr41_comprobante values(3,1,'2016/09/15','','A Pagar','2016/12/31',500,'M');
377 INSERT INTO gr41_comprobante_conl values(3,1,31);
378 INSERT INTO gr41_linea_comprobante values(1,'',1,200,3,1);
379 INSERT INTO gr41_linea_comprobante values(2,'',1,300,3,1);
380
381*/
382
383---------------------------------SERVICIO 3--------------------------------------------------------------------------------------------------
384
385 CREATE FUNCTION TRFN_GR41_ConsistenciaRolCliente()
386 RETURNS TRIGGER AS $$
387 BEGIN
388 IF ( EXISTS (select id_persona from gr41_persona where (id_persona = NEW.id_persona) AND (rol='E')))
389 THEN RAISE EXCEPTION 'La persona posee rol de empleado, no puede ser insertado como cliente';
390 END IF;
391 RETURN NULL;
392 END; $$ LANGUAGE plpgsql;
393
394 CREATE TRIGGER TR_GR41_cliente_ConsistenciaRolCliente
395 AFTER INSERT OR UPDATE ON GR41_cliente
396 FOR EACH ROW EXECUTE PROCEDURE TRFN_GR41_ConsistenciaRolCliente();
397
398 CREATE FUNCTION TRFN_GR41_ConsistenciaRolClienteMod()
399 RETURNS TRIGGER AS $$
400 BEGIN
401 IF (OLD.id_persona <> NEW.id_persona)
402 THEN RAISE EXCEPTION 'No se permite realizar un cambio de id';
403 END IF;
404 RETURN NULL;
405 END; $$ language plpgsql;
406
407 CREATE TRIGGER TR_GR41_Cliente_ConsistenciaRolClienteMod
408 AFTER UPDATE ON GR41_Cliente
409 FOR EACH ROW EXECUTE PROCEDURE TRFN_GR41_ConsistenciaRolClienteMod();
410
411 ---------------------------------------------------------------------------
412
413 CREATE FUNCTION TRFN_GR41_ConsistenciaRolEmpleado()
414 RETURNS TRIGGER AS $$
415 BEGIN
416 IF ( EXISTS (select id_persona from gr41_persona where (id_persona = NEW.id_persona) AND (rol='C')))
417 THEN RAISE EXCEPTION 'La persona posee rol de cliente, no puede ser insertado como empleado';
418 END IF;
419 RETURN NULL;
420 END; $$ language plpgsql;
421
422 CREATE TRIGGER TR_GR41_Empleado_ConsistenciaRolEmpleado
423 AFTER INSERT ON GR41_Empleado
424 FOR EACH ROW EXECUTE PROCEDURE TRFN_GR41_ConsistenciaRolEmpleado();
425
426 CREATE FUNCTION TRFN_GR41_ConsistenciaRolEmpleadoMod()
427 RETURNS TRIGGER AS $$
428 BEGIN
429 IF (OLD.id_persona <> NEW.id_persona)
430 THEN RAISE EXCEPTION 'No se permite realizar un cambio de id';
431 END IF;
432 RETURN NULL;
433 END; $$ LANGUAGE plpgsql;
434
435 CREATE TRIGGER TR_GR41_Empleado_ConsistenciaRolEmpleadoMod
436 AFTER UPDATE ON GR41_Empleado
437 FOR EACH ROW EXECUTE PROCEDURE TRFN_GR41_ConsistenciaRolEmpleadoMod();
438
439-------------------NUESTRAS RESTRICCIONES-----------------------------------------------------------------------------------
440
441 --1)Considerar que el tipo de comprobante coincida con que si tiene o no lineas
442
443 CREATE FUNCTION TRFN_GR41_TipoCONLValido()
444 RETURNS TRIGGER AS $$
445 BEGIN
446 IF ( new.id_tcomp = 2 )
447 THEN RAISE EXCEPTION 'No podes insertar Recibo como comprobante con linea';
448 END IF;
449 RETURN NEW;
450 END; $$ LANGUAGE plpgsql;
451
452 CREATE TRIGGER TR_GR41_verificarTipoCONL
453 AFTER INSERT ON GR41_COMPROBANTE_CONL
454 FOR EACH ROW EXECUTE PROCEDURE TRFN_GR41_TipoCONLValido();
455
456 CREATE FUNCTION TRFN_GR41_TipoSINLValido()
457 RETURNS TRIGGER AS $$
458 BEGIN
459 IF ( new.id_tcomp = 1 )
460 THEN RAISE EXCEPTION 'No podes insertar Facturas en comprobantes sin lineas';
461 ELSIF ( new.id_tcomp = 3 )
462 THEN RAISE EXCEPTION 'No podes insertar Remitos en comprobantes sin lineas';
463 END IF;
464 RETURN NEW;
465 END; $$ LANGUAGE plpgsql;
466
467 CREATE TRIGGER TR_GR41_verificarTipoSINL
468 AFTER INSERT ON GR41_COMPROBANTE_SINL_TURNO
469 FOR EACH ROW EXECUTE PROCEDURE TRFN_GR41_TipoSINLValido();
470
471 --2)El id_comp de comprobante no debe tener agujeros, es decir debe respetar una secuencia
472
473 CREATE FUNCTION TRFN_GR41_secuenciaIdComp()
474 RETURNS TRIGGER AS $$
475 BEGIN
476 IF ( new.id_tcomp = 1 )
477 THEN new.id_comp := nextval('SEC_idfactura');
478 ELSIF ( new.id_tcomp = 2 )
479 THEN new.id_comp := nextval('SEC_idrecibo');
480 ELSIF ( new.id_tcomp = 3 )
481 THEN new.id_comp := nextval('SEC_idremito');
482 END IF;
483 RETURN NEW;
484 END; $$ LANGUAGE plpgsql;
485
486 CREATE TRIGGER TR_GR41_verificarIDcomp
487 BEFORE INSERT ON GR41_COMPROBANTE
488 FOR EACH ROW EXECUTE PROCEDURE TRFN_GR41_secuenciaIdComp();
489
490 --3)El tipo_comprobante de la tabla gr41_comprobante debe coincidir con el id_tcomp segun su valor
491
492 ALTER TABLE GR41_COMPROBANTE
493 ADD CONSTRAINT CHK_ConsistenciaTipoComp CHECK ( (id_tcomp=1 AND tipo_comprobante='F')
494 OR (id_tcomp=2 AND tipo_comprobante='R')
495 OR (id_tcomp=3 AND tipo_comprobante='M'));
496
497
498---------------------------------VISTA 1--------------------------------------------------------------------------------------------------
499
500 CREATE VIEW GR41_VISTA_COMPROBANTES_CL
501 AS SELECT C.id_persona, C.saldo, C.cuit, O.id_tcomp,
502 O.id_comp, O.fecha, O.comentario, O.estado, O.fecha_vencimiento,
503 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
504 FROM GR41_CLIENTE C
505 NATURAL JOIN GR41_COMPROBANTE_CONL L
506 NATURAL JOIN GR41_COMPROBANTE O
507 NATURAL JOIN GR41_LINEA_COMPROBANTE E;
508
509 --UNIR LAS 3 TABLAS
510------------------------------VISTA 2--------------------------------------------------------------------------------------------------
511
512 CREATE VIEW GR41_VISTA_CLIENTES AS
513 SELECT *
514 FROM gr41_cliente NATURAL JOIN gr41_persona;
515
516 CREATE VIEW GR41_VISTA_EMPLEADOS AS
517 SELECT *
518 FROM gr41_empleado NATURAL JOIN gr41_persona;
519
520------------------------------VISTA 3--------------------------------------------------------------------------------------------------
521
522 CREATE VIEW GR41_VISTA_CLIENTE_SALDO_DEUDOR AS
523 SELECT *
524 FROM gr41_persona
525 WHERE id_persona in ( SELECT c.id_persona
526 FROM gr41_cliente c
527 WHERE ( c.saldo > 0 ))