SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `testdb` DEFAULT CHARACTER SET utf8 ;
USE `testdb` ;

-- -----------------------------------------------------
-- Table `testdb`.`results`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `testdb`.`students` ;

CREATE TABLE IF NOT EXISTS `testdb`.`students` (
  `studentid` VARCHAR(64) NOT NULL,
  `result_date` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`studentid`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `testdb`.`exam`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `testdb`.`exam` ;

CREATE TABLE IF NOT EXISTS `testdb`.`exam` (
  `studentid` VARCHAR(64) NOT NULL,
  `score` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`studentid`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

DELIMITER ;

USE `testdb`;
INSERT INTO students(studentid) values(1100);
INSERT INTO exam(studentid, score) values(1100, 40);


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

USE `testdb`;

DELIMITER $$

USE `testdb`$$
DROP TRIGGER IF EXISTS `testdb`.`update_status_on_insert` $$
USE `testdb`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `testdb`.`update_status_on_insert`
AFTER INSERT ON `testdb`.`exam`
FOR EACH ROW
BEGIN
        UPDATE students SET students.result_date = UTC_TIMESTAMP()
        WHERE students.studentid = NEW.studentid;

    END$$


USE `testdb`$$
DROP TRIGGER IF EXISTS `testdb`.`update_status_on_update` $$
USE `testdb`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `testdb`.`update_status_on_update`
AFTER UPDATE ON `testdb`.`exam`
FOR EACH ROW
BEGIN
        UPDATE students SET students.result_date = UTC_TIMESTAMP()
        WHERE students.studentid = NEW.studentid;

    END$$

DELIMITER ;