MySQL After Insert Trigger Not Working

Question

I have the following two tables, and a trigger which should insert a record into the Student table when a record is inserted into the User table with a non-null value in StudentID. MySQL reports no syntax errors on the trigger and an insert into the User table works as expected, but does not trigger an insert into the Student table.
I have tried using BEFORE and AFTER INSERT to no effect. I am using phpMyAdmin version 3.5.2.2. Any help would be greatly appreciated.

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 ;

@Winston I tried BEFORE INSERT with no success. Do I need to make any other syntax changes to the trigger when using BEFORE INSERT as compared with AFTER INSERT? — Aprish

Ross Smith II Ross Smith II · Accepted Answer · 2013-02-22T23:54:55

It does need to be an AFTER trigger, but

IF (NEW.StudentID <> NULL) THEN

will not give you what you want. It will always return false. Instead, use:

IF (NEW.StudentID IS NOT NULL) THEN

or the more cryptic:

IF (NOT IFNULL(NEW.StudentID)) THEN

or the more esoteric:

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

Here's it in action:

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:~$

MySQL After Insert Trigger Not Working

1 Answers