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