BD - REPÀS: Triggers

-- 1)

CREATE OR REPLACE FUNCTION check_conditions() RETURNS trigger AS $$
DECLARE

BEGIN
    IF NOT EXISTS (SELECT * FROM empleats2 e2 WHERE e2.ciutat2 = OLD.ciutat2) THEN
        DELETE FROM empleats1 e1 WHERE e1.ciutat1 = OLD.ciutat2;
    END IF;
    RETURN NULL;
END;
$$LANGUAGE plpgsql;


CREATE TRIGGER trig AFTER UPDATE OF ciutat2 OR DELETE ON empleats2
FOR EACH ROW EXECUTE PROCEDURE check_conditions();

-- 2)

CREATE OR REPLACE FUNCTION check_conditions() RETURNS trigger AS $$
DECLARE
    missatge VARCHAR(100);
BEGIN
    IF NOT EXISTS (SELECT * FROM empleats2 e2 WHERE e2.ciutat2 = NEW.ciutat1) THEN
        SELECT texte into missatge FROM missatgesExcepcions WHERE num = 1;
        RAISE EXCEPTION '%', missatge;
    END IF;

    RETURN NEW;

    EXCEPTION
        WHEN raise_exception THEN
            RAISE EXCEPTION '%', missatge;
END;
$$LANGUAGE plpgsql;


CREATE TRIGGER trigger BEFORE INSERT OR UPDATE OF ciutat1 ON empleats1
FOR EACH ROW EXECUTE PROCEDURE check_conditions();

-- 3)

CREATE OR REPLACE FUNCTION check_conditions1() 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_conditions2() RETURNS trigger AS $$
DECLARE
    missatge VARCHAR(50);
    sou_total INTEGER;
    sou_esborrats INTEGER;
BEGIN
    UPDATE temp
    SET y = y + OLD.salari;
    SELECT x, y INTO sou_total, sou_esborrats FROM temp;
    IF sou_esborrats >= sou_total - sou_esborrats THEN
        SELECT texte INTO missatge FROM missatgesExcepcions WHERE num = 1;
        RAISE EXCEPTION '%', missatge;
    END IF;

    RETURN OLD;

        EXCEPTION
                WHEN raise_exception THEN
                        RAISE EXCEPTION '%', missatge;

END;
$$LANGUAGE plpgsql;

CREATE TRIGGER trigger1 BEFORE DELETE ON empleats
FOR EACH STATEMENT EXECUTE PROCEDURE check_conditions1();

CREATE TRIGGER trigger2 BEFORE DELETE ON empleats
FOR EACH ROW EXECUTE PROCEDURE check_conditions2();

-- 4)

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

    EXCEPTION
        WHEN raise_exception THEN
            RAISE EXCEPTION '%', missatge;
END;
$$LANGUAGE plpgsql;


CREATE TRIGGER trigger BEFORE DELETE ON empleats
FOR EACH STATEMENT EXECUTE PROCEDURE check_conditions();

-- 5)

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

    EXCEPTION
        WHEN raise_exception THEN
            RAISE EXCEPTION '%', missatge;
END;
$$LANGUAGE plpgsql;

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

-- 6)

CREATE or replace FUNCTION auditoria() RETURNS trigger AS $$
DECLARE
    us char(30);
BEGIN
    us := (select usuari from usuari_actual);
    NEW.usuari = us;
    RETURN NEW;
END;
$$LANGUAGE plpgsql;

CREATE TRIGGER ex172 BEFORE INSERT ON treballadors
FOR EACH ROW EXECUTE PROCEDURE auditoria();

-- 7)

CREATE OR REPLACE FUNCTION check_conditions() RETURNS trigger AS $$
DECLARE
    a_usuari CHAR(10);
BEGIN
    IF (TG_OP = 'INSERT') THEN
        SELECT usuari INTO a_usuari FROM usuari_actual;
        INSERT INTO auditoria VALUES(NEW.num_treballador, a_usuari);
    END IF;
    IF (TG_OP = 'DELETE') THEN
        DELETE FROM auditoria a WHERE a.num_treballador = OLD.num_treballador;
    END IF;
    RETURN NULL;
END;
$$LANGUAGE plpgsql;

CREATE TRIGGER trigger AFTER INSERT OR DELETE ON treballadors
FOR EACH ROW EXECUTE PROCEDURE check_conditions();

-- 8)

CREATE OR REPLACE FUNCTION check_conditions() RETURNS trigger AS $$
DECLARE
    a_usuari CHAR(10);
BEGIN
    SELECT usuari INTO a_usuari FROM usuari_actual;
    NEW.usuari = a_usuari;
    RETURN NEW;
END;
$$LANGUAGE plpgsql;

CREATE TRIGGER trigger BEFORE INSERT ON treballadors
FOR EACH ROW EXECUTE PROCEDURE check_conditions();

-- JosepRivaille