CREATE SCHEMA IF NOT EXISTS `174exam1`;

USE `174exam1`;

#1
CREATE TABLE IF NOT EXISTS Department(
    dnumber     int,
    dname       varchar(20),
    location    varchar(20),
    noOfEmp     int,
    PRIMARY KEY(dnumber)
);

CREATE TABLE IF NOT EXISTS Employee(
    ssn         int,
    name        varchar(20),
    address     varchar(20),
    phone       int(9),
    dno         int,
    PRIMARY KEY(ssn),
    FOREIGN KEY(dno) REFERENCES Department(dnumber)
);

TRUNCATE Department;
TRUNCATE Employee;

INSERT INTO Department
VALUES (01, 'Biology', 'Sequoia', 21);

INSERT INTO Department
VALUES (02, 'Computer Science', 'Riverside', 11);

INSERT INTO Employee
VALUES (123456789, 'Eric', '1 Address', 123456789, 02);

INSERT INTO Employee
VALUES (111111111, 'Janet', '2 Address', 111111111, 01);

INSERT INTO Employee
VALUES (987654321, 'Coach', '3 Address', 987654321, 02);

#Test #2
INSERT INTO Employee
VALUES (222222222, 'Diane', '4 Address', 222222222, 1);

#Test #3
DELETE FROM Employee
WHERE ssn = 222222222;

#Test #4
UPDATE Employee
SET dno = 2
WHERE ssn = 222222222;

SELECT * from Department;

UPDATE Department
SET noOfEmp = 0
WHERE dnumber = 2;

#2 WHY WONT THIS WORK
DELIMITER $
CREATE TRIGGER ins_t
    AFTER INSERT
    ON Employee
    FOR EACH ROW
    BEGIN
        UPDATE Department, Employee
        SET noOfEmp = noOfEmp + 1
        WHERE dnumber = dno;
    END $
DELIMITER ;

#3
DELIMITER $
CREATE TRIGGER del_t
    AFTER DELETE
    ON Employee
    FOR EACH ROW
    BEGIN
        UPDATE Department, Employee
        SET noOfEmp = noOfEmp - 1
        WHERE Department.dnumber = Employee.dno;
    END $
DELIMITER ;

#4
DELIMITER $
CREATE TRIGGER upd_t
    AFTER UPDATE
    ON Employee
    FOR EACH ROW
    BEGIN
        UPDATE Department, Employee
        SET noOfEmp = noOfEmp
        WHERE Department.dnumber = Employee.dno;
    END $
DELIMITER ;