Aulas práticas - Ficha 5 - Soluções

CREATE TRIGGER beforeStreamInsertion
BEFORE INSERT ON STREAM FOR EACH ROW
BEGIN
  DECLARE error CONDITION FOR SQLSTATE '99999';
  IF YEAR(NEW.StreamDate) < 2017 THEN
    SIGNAL error SET MESSAGE_TEXT = 'Invalid year!';
  END IF;
  IF NEW.Charge < 0 THEN
    SIGNAL error SET MESSAGE_TEXT = 'Invalid charge value!';
  END IF;
END $

CREATE TRIGGER beforeStreamUpdate
BEFORE UPDATE ON STREAM FOR EACH ROW
BEGIN
  DECLARE error CONDITION FOR SQLSTATE '99999';
  IF YEAR(NEW.StreamDate) < 2017 THEN
    SIGNAL error SET MESSAGE_TEXT = 'Invalid year!';
  END IF;
  IF NEW.Charge < 0 THEN
    SIGNAL error SET MESSAGE_TEXT = 'Invalid charge value!';
  END IF;
  IF OLD.StreamId <> NEW.StreamId THEN
    SIGNAL error SET MESSAGE_TEXT = 'Stream Id cannot be changed!';
  END IF;
END $

CREATE TRIGGER afterDepartmentInsert
AFTER INSERT ON DEPARTMENT FOR EACH ROW
BEGIN
   DECLARE ceo_id INT;
   SELECT StaffId INTO ceo_id FROM STAFF WHERE Job = 'CEO';

   UPDATE STAFF
   SET Supervisor = ceo_id
   WHERE StaffId = NEW.Manager;
END $

CREATE TRIGGER afterDepartmentUpdate
AFTER UPDATE ON DEPARTMENT FOR EACH ROW
BEGIN
   DECLARE ceo_id INT;
   SELECT StaffId INTO ceo_id FROM STAFF WHERE Job = 'CEO';

   UPDATE STAFF
   SET Supervisor = ceo_id
   WHERE StaffId = NEW.Manager;
END $