BD - Pràctica de disparadors

-- 1 -----------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION check_restriction() RETURNS trigger AS $$
DECLARE
    missatge VARCHAR(100);
BEGIN
    SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=1;
    IF (TG_OP = 'DELETE') THEN
        IF (OLD.nempl = 123) THEN RAISE EXCEPTION '%', missatge;
        END IF;
        RETURN OLD;
    END IF;
    IF (TG_OP = 'UPDATE') THEN
        IF (OLD.nempl = 123 AND NEW.nempl != 123) THEN RAISE EXCEPTION '%', missatge;
        END IF;
        RETURN NEW;
    END IF;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER trig1 BEFORE DELETE OR UPDATE OF nempl ON empleats
FOR EACH ROW EXECUTE PROCEDURE check_restriction();


-- 2 -----------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION check_condition2() RETURNS trigger AS $$
DECLARE
    dia_actual char(10);
    missatge varchar(50);
BEGIN
    SELECT dia INTO dia_actual FROM dia;
    IF (dia_actual = 'dijous') THEN
        SELECT texte INTO missatge FROM missatgesExcepcions WHERE num = 1;
        RAISE EXCEPTION '%', missatge;
    END IF;
    RETURN NULL;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER trigger2 BEFORE DELETE ON empleats
FOR EACH STATEMENT EXECUTE PROCEDURE check_condition2();


-- 3 -----------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION calcul_sou_abans() RETURNS trigger AS $$
BEGIN
    DELETE FROM temp;
    INSERT INTO temp(x, y) SELECT SUM(salari), 0 FROM empleats;
    RETURN NULL;
END $$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION check_condition3() RETURNS trigger AS $$
DECLARE
    sou_abans1 INTEGER;
    suma_esb INTEGER;
    missatge VARCHAR(50);
BEGIN
    UPDATE temp
    SET y = y + OLD.salari;
    SELECT x, y INTO sou_abans1, suma_esb FROM temp;
    IF (suma_esb >= (sou_abans1 - suma_esb)) THEN
        SELECT texte INTO missatge FROM missatgesExcepcions WHERE num = 1;
        RAISE EXCEPTION '%', missatge;
    END IF;
    RETURN OLD;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER trigger4 BEFORE DELETE ON empleats
FOR EACH STATEMENT EXECUTE PROCEDURE calcul_sou_abans();

CREATE TRIGGER trigger3 BEFORE DELETE ON empleats
FOR EACH ROW EXECUTE PROCEDURE check_condition3();

-- JosepRivaille