USE lab09;

#Step 1: Create new audit table (minor formatting changes for consistancy)
CREATE TABLE professor_audit_trail (
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
	ProfessorName VARCHAR(255) NOT NULL,
	ProgramName VARCHAR(255) NOT NULL
    );

#Step 2: Create trigger to save old data whenever a record is deleted from professor table
DELIMITER $$
CREATE TRIGGER DeletedProfTrigger
	BEFORE DELETE ON professor
    FOR EACH ROW
	BEGIN
        INSERT INTO professor_audit_trail
        SET id = OLD.ProfessorID,
        ProfessorName = OLD.ProfessorName,
        ProgramName = OLD.ProfessorProgram;
	END$$
DELIMITER ;