Exercici: Triggers

José Manuel Pérez Barberá

jm.perezbarbera@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’executa BEFORE INSERT a la taula employees.
Comprova que el camp email conté un “@”.
Si no, llança un error amb RAISE_APPLICATION_ERROR.

Solució

CREATE OR REPLACE TRIGGER trg_validar_email
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF INSTR(:NEW.email, '@') = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Email no vàlid');
    END IF;
END;
/

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’executa BEFORE INSERT a la taula employees.
Si hire_date és NULL → assigna SYSDATE.
Mostra un missatge amb DBMS_OUTPUT.PUT_LINE indicant que s’ha assignat la data.

Solució

CREATE OR REPLACE TRIGGER trg_hire_date
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF :NEW.hire_date IS NULL THEN
        :NEW.hire_date := SYSDATE;
        DBMS_OUTPUT.PUT_LINE('Assignada hire_date automàticament a ' || :NEW.first_name);
    END IF;
END;
/

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 NUMBER
old_salary NUMBER
new_salary NUMBER
changed_at DATE

Crea un trigger trg_audit_salary que:

S’executa AFTER UPDATE a employees.
Registra en audit_salary els canvis de salari.
Només quan el salari canvia.

Solució

CREATE TABLE audit_salary (
    emp_id      NUMBER,
    old_salary  NUMBER,
    new_salary  NUMBER,
    changed_at  DATE
);
/

CREATE OR REPLACE TRIGGER trg_audit_salary
AFTER UPDATE OF salary ON employees
FOR EACH ROW
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, SYSDATE);
    END IF;
END;
/

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’executa BEFORE INSERT a employees.
Si l’empleat entra amb la posició Jefe de proyecto i el salari és menor de 38000 → li assigna un bonus de 500 automàticament.
Mostra un missatge amb DBMS_OUTPUT.PUT_LINE.

Solució

CREATE OR REPLACE TRIGGER trg_bonus_departament
BEFORE INSERT ON employees
FOR EACH ROW
DECLARE
    v_position_id NUMBER;
BEGIN
    SELECT position_id INTO v_position_id FROM positions WHERE position = 'Jefe de proyecto';
    IF :NEW.position_id = v_position_id AND :NEW.salary < 38000 THEN
        :NEW.salary := :NEW.salary + 500;
        DBMS_OUTPUT.PUT_LINE('Assignat bonus de 500 a ' || :NEW.first_name);
    END IF;
END;
/

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 3300

Exercici 5: Trigger amb cursor i control de condicions¶

Crea un trigger trg_revisio_departament que:

S’executa AFTER UPDATE a employees.
Si el salari supera 38000 i la posició és Jefe de proyecto, envia un missatge amb DBMS_OUTPUT.PUT_LINE indicant que cal revisar el salari.
Recorre tots els empleats modificats dins de l’actualització.

Solució

CREATE OR REPLACE TRIGGER trg_revisio_departament
AFTER UPDATE OF salary ON employees
FOR EACH ROW
DECLARE
    v_position_id NUMBER;
BEGIN
    SELECT position_id INTO v_position_id FROM positions WHERE position = 'Jefe de proyecto';
    IF :NEW.salary > 38000 AND :NEW.position_id = position_id THEN
        DBMS_OUTPUT.PUT_LINE('Revisar salari de ' || :NEW.first_name || ': ' || :NEW.salary);
    END IF;
END;
/

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.