MySQL AFTER INSERT Trigger "Unknown Column"

Question

I have researched the issue and it usually relates to the column names not matching up. I've checked and re-checked my column names to make sure they match up. I can't figure out why it's complaining. Maybe all I need is a 2nd pair of eyes, or a long lunch-break?

Tables:

DROP TABLE IF EXISTS `User`;
CREATE TABLE `User` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `userType` varchar(20) NOT NULL,
  `firstName` varchar(20) NOT NULL,
  `lastName` varchar(40) NOT NULL,
  `email` varchar(40) DEFAULT NULL,
  `contributorRating` int(1) DEFAULT NULL,
  `dateCreated` timestamp DEFAULT CURRENT_TIMESTAMP,
  `isFlagged` bit(1) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `userType` (`userType`),
  CONSTRAINT `User_ibfk_1` FOREIGN KEY (`userType`) REFERENCES `UserType` (`userType`)  ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `User_Project`;
CREATE TABLE `User_Project` (
  `projectID` int(11) NOT NULL,
  `userID` int(11) NOT NULL,
  `joinDate` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`projectID`,`userID`),
  KEY `userID` (`userID`),
  CONSTRAINT `User_Project_ibfk_1` FOREIGN KEY (`projectID`) REFERENCES `Project` (`ID`) ON UPDATE CASCADE,
  CONSTRAINT `User_Project_ibfk_2` FOREIGN KEY (`userID`) REFERENCES `User` (`ID`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `Project`;
CREATE TABLE `Project` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `userID` int(11) NOT NULL,
  `projectName` varchar(40) NOT NULL,
  `frequencyType` varchar(10) DEFAULT NULL,
  `projectType` varchar(20) NOT NULL,
  `projectDescription` text NOT NULL,
  `dateCreated` timestamp DEFAULT CURRENT_TIMESTAMP,
  `startDate` timestamp DEFAULT 0,
  `endDate` timestamp DEFAULT 0,
  `isActive` tinyint(1) unsigned NOT NULL,
  `visibility` varchar(20) DEFAULT 'private',
  `region` varchar(20) NOT NULL,
  `isFlagged` bit(1) DEFAULT NULL,
  `lastUpdate` timestamp DEFAULT 0,
  PRIMARY KEY (`ID`),
  KEY `userID` (`userID`),
  KEY `frequencyType` (`frequencyType`),
  KEY `projectType` (`projectType`),
  CONSTRAINT `Project_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `User` (`ID`) ON UPDATE CASCADE,
  CONSTRAINT `Project_ibfk_2` FOREIGN KEY (`frequencyType`) REFERENCES `Frequency` (`frequency`) ON UPDATE CASCADE,
  CONSTRAINT `Project_ibfk_3` FOREIGN KEY (`projectType`) REFERENCES `ProjectType` (`projectType`) ON UPDATE CASCADE,
  CONSTRAINT `Project_ibfk_4` FOREIGN KEY (`visibility`) REFERENCES `Visibility` (`visibility`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Trigger:

 DROP TRIGGER IF EXISTS ins_Project;
 CREATE TRIGGER ins_Project AFTER INSERT ON `Project`
 FOR EACH ROW 
 BEGIN
  SET NEW.lastUpdate = NOW();
  INSERT IGNORE INTO User_Project(projectID,userID,joinDate) VALUES (NEW.ID,NEW.userID,NOW());
 END;

Error:

ERROR 1054 (42S22): Unknown column 'NEW.ID' in 'field list'

Try creating the 'Project' table first and then create 'User_Project' table to avoid foreign key violation. — Slowcoder
– Slowcoder, Commented Feb 20, 2013 at 22:16
the database is already up and running (correctly)...it's the trigger I'm concerned about — Griff McGriff
– Griff McGriff, Commented Feb 20, 2013 at 22:19

Mike Brant · Accepted Answer · 2013-02-20 22:20:58Z

5

I am not 100% positive on this, because the documentation doesn't really get into this, but I think you are only intended to use

SET NEW.lastUpdate = NOW();

or similar SET to set a field value for the insert row in a BEFORE trigger.

Try splitting that part out into a BEFORE INSERT trigger and see if it works. Perhaps in an after trigger context, calling SET on a record field like that updates the row and loses the record pointer causing your error.

I say this because the MySQL documentation only explicitly states that you can SET a row value during a before trigger. Check this quote from the documentation:

In a BEFORE trigger, you can also change its value with SET NEW.col_name = value if you have the UPDATE privilege for it. This means you can use a trigger to modify the values to be inserted into a new row or that are used to update a row.

answered Feb 20, 2013 at 22:20

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Griff McGriff Over a year ago

That's dead right, and the cause of the error. There definitely needs to be a stronger error message for this.

Slowcoder Over a year ago

Yes right. The are bugs already reported in MySQL to change the error message to be more specific.

Collectives™ on Stack Overflow

MySQL AFTER INSERT Trigger "Unknown Column"

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related