插入触发器后的 MySQL 不工作

Question

我有以下两个表，以及一个触发器，当一条记录插入到 User 表中并且 StudentID 中的值为非空值时，它应该将一条记录插入到 Student 表中。 MySQL 在触发器上报告没有语法错误，并且对 User 表的插入按预期工作，但不会触发对 Student 表的插入。
我试过使用 BEFORE 和 AFTER INSERT 没有效果。我正在使用 phpMyAdmin 版本 3.5.2.2。任何帮助将不胜感激。

DROP TABLE IF EXISTS `User` ;

CREATE  TABLE IF NOT EXISTS `User` (
  `UserID` INT NOT NULL AUTO_INCREMENT ,
  `FirstName` VARCHAR(45) NOT NULL ,
  `LastName` VARCHAR(45) NOT NULL ,
  `eMail` VARCHAR(60) NOT NULL ,
  `StudentID` INT NULL ,
  KEY ix_User_StudentID (StudentID),
  PRIMARY KEY (`UserID`)
)
ENGINE = InnoDB;  

DROP TABLE IF EXISTS `Student` ;

CREATE  TABLE IF NOT EXISTS `Student` (
  `StudentID` INT NOT NULL ,
  `UserID` INT NOT NULL ,
  PRIMARY KEY (`StudentID`) ,
  CONSTRAINT `fk_Student_User1`
    FOREIGN KEY (`StudentID` )
    REFERENCES `User` (`StudentID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
  CONSTRAINT `fk_Student_User2`
    FOREIGN KEY (`UserID`)
    REFERENCES `User` (`UserID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
ENGINE = InnoDB;

DROP TRIGGER IF EXISTS after_insert_user;

DELIMITER //
CREATE TRIGGER after_insert_user 
AFTER INSERT ON `User`
FOR EACH ROW BEGIN
    IF (NEW.StudentID <> NULL) THEN
        INSERT INTO Student VALUES (NEW.StudentID, NEW.UserID); 
    END IF;
END;
//

DELIMITER ;

Answer 1

它确实需要是一个 AFTER 触发器，但是

IF (NEW.StudentID <> NULL) THEN

不会给你你想要的。它总是会返回 false。相反，使用:

IF (NEW.StudentID IS NOT NULL) THEN

或者更神秘的:

IF (NOT IFNULL(NEW.StudentID)) THEN

或更深奥的:

IF (NOT NEW.StudentID <=> NULL) THEN

这是实际操作:

ross@pv1:~$ mysql -vvv < 15034839.sql 
--------------
DROP TABLE IF EXISTS `Student`
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
DROP TABLE IF EXISTS `User`
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
CREATE  TABLE IF NOT EXISTS `User` (
  `UserID` INT NOT NULL AUTO_INCREMENT ,
  `FirstName` VARCHAR(45) NOT NULL ,
  `LastName` VARCHAR(45) NOT NULL ,
  `eMail` VARCHAR(60) NOT NULL ,
  `StudentID` INT NULL ,
  KEY ix_User_StudentID (StudentID),
  PRIMARY KEY (`UserID`)
)
ENGINE = InnoDB
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
CREATE  TABLE IF NOT EXISTS `Student` (
  `StudentID` INT NOT NULL ,
  `UserID` INT NOT NULL ,
  PRIMARY KEY (`StudentID`) ,
  CONSTRAINT `fk_Student_User1`
    FOREIGN KEY (`StudentID` )
    REFERENCES `User` (`StudentID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Student_User2`
    FOREIGN KEY (`UserID`)
    REFERENCES `User` (`UserID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
ENGINE = InnoDB
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
DROP TRIGGER IF EXISTS after_insert_user
--------------

Query OK, 0 rows affected, 1 warning (0.00 sec)

--------------
CREATE TRIGGER after_insert_user
AFTER INSERT ON `User`
FOR EACH ROW BEGIN
    IF (NEW.StudentID IS NOT NULL) THEN
        INSERT INTO Student VALUES (NEW.StudentID, NEW.UserID);
    END IF;
END;
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
INSERT INTO User VALUES (NULL, 'first', 'last', 'email', 123)
--------------

Query OK, 1 row affected (0.01 sec)

--------------
SELECT * FROM Student
--------------

+-----------+--------+
| StudentID | UserID |
+-----------+--------+
|       123 |      1 |
+-----------+--------+
1 row in set (0.00 sec)

Bye
ross@pv1:~$