create database company;
use company;
create table employe(
SSN int ,
Nom varchar(20),
prenom varchar(20),
date_de_naissance date,
adresse varchar(50),
sexe varchar(10),
salaire int,
superSSN int,
dNumero int,
date_embauche date
);
create table Departement(
dNumero int,
dnom varchar(50),
chefSSN int,
dateDebutChef date,
NbrEmployees int
);
create table DEPTLOCATIONS (dNumero int, Dlocation varchar(100));
create table Projet(
Pnumero int,
pNom varchar(20),
plocation varchar(100),
dNumero int
);
create table Travail(
SSN int,
Pnumero int,
Heures float
);
#1
CREATE TRIGGER age_check
BEFORE INSERT ON employe
FOR EACH ROW
BEGIN
IF (DATEDIFF(YEAR, NEW.date_de_naissance, CURDATE()) < 18) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Employee must be at least 18 years
old.';
END IF;
END;
#2
CREATE TRIGGER supervisor_age_check
BEFORE INSERT ON employe
FOR EACH ROW
BEGIN
DECLARE supervisor_dob DATE;
SELECT date_de_naissance INTO supervisor_dob FROM employe WHERE SSN =
[Link];
IF (NEW.date_de_naissance > supervisor_dob) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Supervisor must be older than
employee.';
END IF;
END;
#3
CREATE TRIGGER salary_check
BEFORE INSERT ON employe
FOR EACH ROW
BEGIN
DECLARE supervisor_salary INT;
SELECT salaire INTO supervisor_salary FROM employe WHERE SSN = [Link];
IF ([Link] > supervisor_salary) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Employee salary must be less than
supervisor salary.';
END IF;
END;
#4
CREATE TRIGGER department_manager_check
BEFORE INSERT ON Departement
FOR EACH ROW
BEGIN
DECLARE manager_department INT;
SELECT dNumero INTO manager_department FROM employe WHERE SSN = [Link];
IF (manager_department != [Link]) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Department manager must be an
employee of the department.';
END IF;
END;
#5
CREATE TRIGGER project_location_check
BEFORE INSERT ON Projet
FOR EACH ROW
BEGIN
DECLARE department_locations VARCHAR(100);
SELECT GROUP_CONCAT(Dlocation) INTO department_locations
FROM DEPTLOCATIONS
WHERE dNumero = [Link];
IF (NOT FIND_IN_SET([Link], department_locations)) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Project location must be one of the
locations of its department.';
END IF;
END;
#6
CREATE TRIGGER supervisor_hire_date_check
BEFORE INSERT ON employe
FOR EACH ROW
BEGIN
DECLARE supervisor_hire_date DATE;
SELECT date_embauche INTO supervisor_hire_date FROM employe WHERE SSN =
[Link];
IF (DATEDIFF(YEAR, supervisor_hire_date, NEW.date_embauche) < 1) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Supervisor must be hired at least
one year before each employee they supervise.';
END IF;
END;
#7
CREATE TRIGGER update_department_employees
AFTER INSERT, UPDATE, DELETE ON employe
FOR EACH ROW
BEGIN
DECLARE department_employees INT;
SELECT COUNT(*) INTO department_employees FROM employe WHERE dNumero =
[Link];
UPDATE Departement SET NbrEmployees = department_employees WHERE dNumero =
[Link];
END;
#8
CREATE TRIGGER update_employee_supervisor
AFTER INSERT, UPDATE, DELETE ON employe
FOR EACH ROW
BEGIN
DECLARE department_manager INT;
SELECT chefSSN INTO department_manager FROM Departement WHERE dNumero =
[Link];
IF ([Link] = department_manager) THEN
UPDATE employe SET superSSN = department_manager WHERE dNumero = [Link]
AND SSN != department_manager;
ELSE
UPDATE employe SET superSSN = department_manager WHERE SSN = [Link];
END IF;
END;
#9
CREATE TRIGGER employee_project_limit
BEFORE INSERT ON Travail
FOR EACH ROW
BEGIN
DECLARE project_count INT;
SELECT COUNT(*) INTO project_count FROM Travail WHERE SSN = [Link];
IF (project_count >= 4) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Employee cannot work on more than 4
projects.';
END IF;
END;