tee 2025_10_22_sql_log.txt

-- trigger

CREATE TABLE courseUpdate(
   cuId        INT NOT NULL AUTO_INCREMENT,
   `type`      CHAR(1),
    courseId    INT NOT NULL,
    rubric      CHAR(4) NOT NULL,
    number      CHAR(4) NOT NULL,
    title       VARCHAR(80) NOT NULL,
    credits     TINYINT NULL,
    ts          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT CourseUpdate_courseId_pk PRIMARY KEY (cuId),
    CONSTRAINT CourseUpdate_deptCode_fk FOREIGN KEY (rubric)
        REFERENCES Department(deptCode));
     
    
-- trigger example.
DELIMITER $$

CREATE TRIGGER update_Course AFTER UPDATE ON Course FOR EACH ROW
BEGIN
   -- code should be more sophisticated.
   INSERT INTO courseUpdate(`type`, courseId, rubric, number, title, credits)
   VALUES('U', new.courseId, new.rubric, new.number, new.title, new.credits);
END $$


CREATE TRIGGER insert_Course AFTER INSERT ON Course FOR EACH ROW
BEGIN
   -- code should be more sophisticated.
   INSERT INTO courseUpdate(`type`, courseId, rubric, number, title, credits)
   VALUES('I', new.courseId, new.rubric, new.number, new.title, new.credits);
END $$

DELIMITER ;

SELECT * FROM Course;
SELECT * FROM courseUpdate;
UPDATE Course

SET number = 2315
WHERE courseId = 2000;

SELECT * FROM Course;
SELECT * FROM courseUpdate;

INSERT INTO Course(courseId, rubric, number, title, credits) VALUES
    (4000,'CSCI',3532,'Advanced Data Structures',3);
  
UPDATE Course
SET number = 3341
WHERE courseId = 4000;

SELECT * FROM course;
SELECT * FROM courseUpdate;

-- Clean up.
DROP TRIGGER update_Course;
DROP TRIGGER insert_Course;

DROP TABLE courseUpdate;

DELETE FROM Course
WHERE CourseId = 4000;

UPDATE Course
SET number = 3333
WHERE courseId = 2000;

SELECT * FROM Course;