⚙️ 4. AUTOMATION & PERFORMANCE
1️⃣ Create Table prueba_update_salarios + Add Column
CREATE TABLE prueba_updates_salarios (
id INT AUTO_INCREMENT PRIMARY KEY,
emp_no INT NOT NULL,
old_salary INT,
NEW_salary INT,
change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE prueba_updates_salarios ADD COLUMN reason VARCHAR(255);
2️⃣ Create View Titulos_Salarios_Empleados
CREATE VIEW Titulos_Salarios_Empleados AS
SELECT E.emp_no AS 'Nº empleado', E.first_name AS 'Nombre', E.last_name AS 'Apellido',
S.salary AS 'Salario', T.title AS 'Título'
FROM employees E
JOIN salaries S ON E.emp_no = S.emp_no
JOIN titles T ON E.emp_no = T.emp_no
WHERE T.to_date = '9999-01-01';
SELECT * FROM Titulos_Salarios_Empleados;
3️⃣ Create Index To Improve Performance
-- OPCIÓN SESIÓN ACTUAL:
SET @@SESSION.wait_timeout = 300;
SET @@SESSION.interactive_timeout = 300;
-- Crear índice para mejorar rendimiento en salaries
CREATE INDEX idx_salaries_emp_date ON salaries (emp_no, from_date);
-- Para eliminarlo más adelante:
-- DROP INDEX idx_salaries_emp_date ON salaries;
4️⃣ Stored Procedure With Transaction For Salary Updates
DELIMITER //
CREATE PROCEDURE UpdateSalary (
IN p_emp_no INT,
IN p_new_salary INT,
IN p_reason VARCHAR(255)
)
BEGIN
DECLARE old_salary INT;
START TRANSACTION;
SELECT salary INTO old_salary
FROM salaries
WHERE emp_no = p_emp_no AND to_date = '9999-01-01'
LIMIT 1;
SAVEPOINT before_update;
UPDATE salaries
SET to_date = CURDATE()
WHERE emp_no = p_emp_no AND to_date = '9999-01-01';
IF ROW_COUNT() = 0 THEN
ROLLBACK TO SAVEPOINT before_update;
SELECT 'Error: No se encontró el salario actual' AS message;
ELSE
INSERT INTO salaries (emp_no, salary, from_date, to_date)
VALUES (p_emp_no, p_new_salary, CURDATE(), '9999-01-01');
IF ROW_COUNT() = 0 THEN
ROLLBACK TO SAVEPOINT before_update;
SELECT 'Error: No se pudo insertar el nuevo salario' AS message;
ELSE
COMMIT;
SELECT 'Salario actualizado correctamente' AS message;
END IF;
END IF;
END //
DELIMITER ;
-- Llamar al procedimiento
-- CALL UpdateSalary(10001, 92000, 'Aumento anual');
5️⃣ Trigger To Log Salary Updates
DELIMITER //
CREATE TRIGGER trigger_actualizar_salario
AFTER INSERT ON salaries
FOR EACH ROW
BEGIN
INSERT INTO prueba_updates_salarios (emp_no, old_salary, new_salary, reason)
VALUES (
NEW.emp_no,
(SELECT salary FROM salaries WHERE emp_no = NEW.emp_no ORDER BY to_date DESC LIMIT 1),
NEW.salary,
'Aumento anual'
);
END //
DELIMITER ;
6️⃣ Trigger To Block Future Hire Dates
DELIMITER //
CREATE TRIGGER trg_prevent_future_hire_date
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF NEW.hire_date > CURDATE() THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Error: No se puede insertar un empleado con fecha futura';
END IF;
END //
DELIMITER ;
-- Prueba del trigger (debería lanzar error)
-- INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date)
-- VALUES (10002, '1995-07-20', 'Ana', 'Gómez', 'F', '2030-01-01');
7️⃣ Session Timeout Settings And Index Creation
-- OPCIÓN SESIÓN ACTUAL:
SET @@SESSION.wait_timeout = 300;
SET @@SESSION.interactive_timeout = 300;
-- Crear índice para mejorar rendimiento en salaries
CREATE INDEX idx_salaries_emp_date ON salaries (emp_no, from_date);
-- Para eliminarlo más adelante:
-- DROP INDEX idx_salaries_emp_date ON salaries;