CREATE DATABASE Triggers2;

USE Triggers2;

CREATE TABLE Emp(
ssn CHAR(10),
name VARCHAR(50) NOT NULL,
salary DECIMAL(8,2) ,
dept_id INT NOT NULL, 
CONSTRAINT minsalary CHECK (salary >=10000),
PRIMARY KEY  (ssn)
);

CREATE TABLE Dept(
id INT,
name CHAR(20) NOT NULL,
manager_ssn CHAR(10),
budget DECIMAL(10,2) DEFAULT 0,
no_of_employee INT DEFAULT 0,

PRIMARY KEY (id)
);

ALTER TABLE  Dept ADD FOREIGN KEY (manager_ssn) REFERENCES Emp(ssn);
ALTER TABLE Emp ADD FOREIGN KEY (dept_id) REFERENCES dept(id);

DELIMITER // 
CREATE TRIGGER Emp_insert 
AFTER INSERT ON Emp 
FOR EACH ROW
BEGIN
UPDATE Dept SET budget= budget + NEW.salary WHERE id=NEW.dept_id;
UPDATE Dept SET no_of_employee= no_of_employee + 1 WHERE id=NEW.dept_id;
END
//


CREATE TRIGGER del_emp 
AFTER DELETE ON Emp 
FOR EACH ROW
BEGIN
UPDATE Dept SET budget= budget - OLD.salary WHERE id=OLD.dept_id;
UPDATE Dept SET no_of_employee= no_of_employee - 1 WHERE id=OLD.dept_id;
END //

CREATE TRIGGER update_emp 
AFTER UPDATE ON Emp 
FOR EACH ROW
BEGIN
UPDATE Dept SET budget= budget - OLD.salary WHERE id=OLD.dept_id;
UPDATE Dept SET budget= budget + NEW.salary WHERE id=NEW.dept_id;
UPDATE Dept SET no_of_employee= no_of_employee - 1 WHERE id=OLD.dept_id;
UPDATE Dept SET no_of_employee= no_of_employee + 1 WHERE id=NEW.dept_id;
END
//

CREATE TRIGGER delete_dept 
BEFORE DELETE ON Dept
FOR EACH ROW
BEGIN
   IF OLD.no_of_employee >0  THEN
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Could NOT DELETE that department'; 
    END IF;
END
//