CREATE or replace FUNCTION comprobar_ciutat2() RETURNS trigger AS $$
BEGIN
DELETE FROM empleats1 WHERE ciutat1=OLD.ciutat2 AND (SELECT count(*) FROM empleats2 WHERE ciutat2=OLD.ciutat2)=0;
RETURN NULL;
END;
$$LANGUAGE plpgsql;
CREATE TRIGGER compciutat2 AFTER DELETE OR UPDATE ON empleats2 FOR EACH ROW EXECUTE PROCEDURE comprobar_ciutat2();
CREATE OR REPLACE FUNCTION assignar_individual(nom char(10),
club char(10)) RETURNS void AS $$
DECLARE
missatge varchar(50);
BEGIN
IF (SELECT COUNT(*) FROM socisclubs WHERE nclub=club) >=10 THEN
SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=1;
RAISE EXCEPTION '%',
missatge;
END IF;
IF (SELECT COUNT(*) FROM socisclubs s,
socis s2 WHERE s.nsoci=s2.nsoci and s.nclub=club AND s2.sexe='F') <
((SELECT COUNT(*) FROM socisclubs s,
socis s2 WHERE s.nsoci=s2.nsoci and s.nclub=club AND s2.sexe='M')+1)
AND (SELECT sexe from socis WHERE nsoci=nom)='M' THEN
SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=2;
RAISE EXCEPTION '%',
missatge;
END IF;
IF (SELECT COUNT(*) FROM socisclubs WHERE nsoci=nom AND nclub=club)<>0 THEN
SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=3;
RAISE EXCEPTION '%',
missatge;
END IF;
IF (SELECT count(*) FROM socis WHERE nsoci=nom)=0 OR (SELECT COUNT(*) FROM clubs WHERE nclub=club)=0 THEN
SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=4;
RAISE EXCEPTION '%',
missatge;
END IF;
INSERT INTO socisclubs values(nom,
club);
IF (SELECT COUNT(*) FROM clubs_amb_mes_de_5_socis where nclub=club)=0 AND (SELECT COUNT(*) FROM socisclubs WHERE nclub=club)>=5 THEN
INSERT INTO clubs_amb_mes_de_5_socis VALUES (club);
END IF;
EXCEPTION
WHEN raise_exception THEN
RAISE EXCEPTION '%',
SQLERRM;
WHEN OTHERS THEN
SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=5;
RAISE EXCEPTION '%',
missatge;
END;
$$LANGUAGE plpgsql;