Exercici: Triggers

Joan Puigcerver Ibáñez

j.puigcerveribanez@edu.gva.es

Llicència: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 🅭

Exercici: Triggers¶

Exercici 1: Trigger de comprovació d'email¶

Crea un trigger trg_validar_email que:
- S'execute BEFORE INSERT a la taula employees.
- Comprove que el camp email conté un @.
- Si no, llance un error amb RAISE EXCEPTION.

Solució

CREATE OR REPLACE FUNCTION validar_email()
RETURNS TRIGGER AS $$
BEGIN
    IF POSITION('@' IN NEW.email) = 0 THEN
        RAISE EXCEPTION 'Email no vàlid';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validar_email
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION validar_email();

Validació d'execució:

INSERT INTO employees(employee_id, first_name, last_name, email, salary)
VALUES (2001, 'Test', 'User', 'testuser', 2500);
-- Ha de llançar error "Email no vàlid"

Exercici 2: Trigger de data de contractació automàtica¶

Crea un trigger trg_hire_date que:
- S'execute BEFORE INSERT a la taula employees.
- Si hire_date és NULL, assigne CURRENT_DATE.
- Mostre un missatge amb RAISE NOTICE indicant que s'ha assignat la data.

Solució

CREATE OR REPLACE FUNCTION assignar_hire_date()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.hire_date IS NULL THEN
        NEW.hire_date := CURRENT_DATE;
        RAISE NOTICE 'Assignada hire_date automàticament a %', NEW.first_name;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_hire_date
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION assignar_hire_date();

Validació d'execució:

INSERT INTO employees(employee_id, first_name, last_name, salary)
VALUES (2002, 'Auto', 'Date', 3000);

SELECT hire_date FROM employees WHERE employee_id = 2002;
-- Ha de mostrar la data actual

Exercici 3: Trigger d'auditoria de salaris¶

Crea una taula audit_salary amb les columnes:
- emp_id INTEGER
- old_salary NUMERIC
- new_salary NUMERIC
- changed_at TIMESTAMP
Crea un trigger trg_audit_salary que:
- S'execute AFTER UPDATE a employees.
- Registre en audit_salary els canvis de salari.
- Només quan el salari canvia.

Solució

CREATE TABLE audit_salary (
    emp_id      INTEGER,
    old_salary  NUMERIC,
    new_salary  NUMERIC,
    changed_at  TIMESTAMP
);

CREATE OR REPLACE FUNCTION audit_salary_canvi()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO audit_salary(emp_id, old_salary, new_salary, changed_at)
        VALUES (NEW.employee_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_salary
AFTER UPDATE OF salary ON employees
FOR EACH ROW
EXECUTE FUNCTION audit_salary_canvi();

Validació d'execució:

UPDATE employees
SET salary = salary + 100
WHERE employee_id = 100;

SELECT * FROM audit_salary WHERE emp_id = 100;
-- Ha de mostrar l'antic i nou salari

Exercici 4: Trigger complex amb regles de negoci¶

Crea un trigger trg_bonus_departament que:
- S'execute BEFORE INSERT a employees.
- Si l'empleat entra amb la posició Jefe de Proyecto i el salari és menor de 38000, li assigne un bonus de 500 automàticament.
- Mostre un missatge amb RAISE NOTICE.

Solució

CREATE OR REPLACE FUNCTION bonus_departament()
RETURNS TRIGGER AS $$
DECLARE
    v_position_id INTEGER;
BEGIN
    SELECT position_id INTO v_position_id
    FROM positions WHERE position_name = 'Jefe de Proyecto';

    IF NEW.position_id = v_position_id AND NEW.salary < 38000 THEN
        NEW.salary := NEW.salary + 500;
        RAISE NOTICE 'Assignat bonus de 500 a %', NEW.first_name;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_bonus_departament
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION bonus_departament();

Validació d'execució:

INSERT INTO employees(employee_id, first_name, last_name, salary, position_id)
VALUES (1001, 'Bonus', 'Test', 30000, 4);

SELECT salary FROM employees WHERE employee_id = 1001;
-- Ha de mostrar 30500

Exercici 5: Trigger amb control de condicions¶

Crea un trigger trg_revisio_departament que:
- S'execute AFTER UPDATE a employees.
- Si el salari supera 38000 i la posició és Jefe de Proyecto, envie un missatge amb RAISE NOTICE indicant que cal revisar el salari.

Solució

CREATE OR REPLACE FUNCTION revisio_departament()
RETURNS TRIGGER AS $$
DECLARE
    v_position_id INTEGER;
BEGIN
    SELECT position_id INTO v_position_id
    FROM positions WHERE position_name = 'Jefe de Proyecto';

    IF NEW.salary > 38000 AND NEW.position_id = v_position_id THEN
        RAISE NOTICE 'Revisar salari de %: %', NEW.first_name, NEW.salary;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_revisio_departament
AFTER UPDATE OF salary ON employees
FOR EACH ROW
EXECUTE FUNCTION revisio_departament();

Validació d'execució:

UPDATE employees
SET salary = 40000
WHERE employee_id = 101 AND position_id = 4;

-- Ha de mostrar: "Revisar salari de <Nom>: 40000"

📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent a la pàgina web.

🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.