MySql - can BEFORE INSERT TRIGGER insert into 2 columns?

Question

Can this trigger be changed so that the sortorder table gets 2 column values (sortOrderId, sortOrder) inserted?

How is the value of sortOrder found?

If it is known and can be inserted into image table then can it also be inserted into the sortorder table?

-- Trigger DDL Statements
DELIMITER $$

USE `nextcart`$$

CREATE
DEFINER=`root`@`localhost`
TRIGGER `nextcart`.`insert_sortorderid` 

BEFORE INSERT ON `nextcart`.`image` 

FOR EACH ROW 
BEGIN
    INSERT INTO sortorder SET sortOrderId = NULL, sortOrder = NEW.sortOrder;

    SET NEW.sortOrderId = (SELECT LAST_INSERT_ID());
END;
$$

CREATE TABLE sortorder:

delimiter $$

CREATE TABLE `sortorder` (
  `sortOrderId` int(11) NOT NULL AUTO_INCREMENT,
  `sortOrder` tinyint(4) NOT NULL,
  PRIMARY KEY (`sortOrderId`),
  KEY `sort_order` (`sortOrderId`,`sortOrder`),
  CONSTRAINT `fk_sortOrderId` FOREIGN KEY (`sortOrderId`) REFERENCES `image` (`imageId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8$$

CREATE TABLE image:

delimiter $$

CREATE TABLE `image` (
  `imageId` int(11) NOT NULL AUTO_INCREMENT,
  `imageFileName` varchar(45) DEFAULT NULL,
  `imagePath` varchar(255) DEFAULT NULL,
  `imageTitle` varchar(100) DEFAULT NULL,
  `imageAlt` varchar(100) DEFAULT NULL,
  `imageWidth` int(11) DEFAULT NULL,
  `imageHeight` int(11) DEFAULT NULL,
  `classId` int(11) DEFAULT NULL,
  `imageSizeId` tinyint(4) NOT NULL,
  `isImageEnabled` bit(1) DEFAULT b'0',
  `sortOrderId` int(11) DEFAULT NULL,
  PRIMARY KEY (`imageId`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8$$

ERROR MESSAGE:

Error 1054: Unknown column 'sortOrder' in 'NEW' SQL Statement:
CREATE TRIGGER insert_sortorderid BEFORE INSERT ON image FOR EACH ROW BEGIN INSERT INTO nextcart.sortorder SET sortOrderId = NULL, sortOrder = NEW.sortOrder; SET NEW.sortOrderId = ( SELECT LAST_INSERT_ID()); END; Error when running failback script. Details follow. Error 1050: Table 'image' already exists SQL Statement: CREATE TABLE image ( imageId int(11) NOT NULL AUTO_INCREMENT,
imageFileName varchar(45) DEFAULT NULL, imagePath varchar(255) DEFAULT NULL, imageTitle varchar(100) DEFAULT NULL, imageAlt varchar(100) DEFAULT NULL, imageWidth int(11) DEFAULT NULL,
imageHeight int(11) DEFAULT NULL, classId int(11) DEFAULT NULL, imageSizeId tinyint(4) NOT NULL, isImageEnabled bit(1) DEFAULT b'0', sortOrderId int(11) DEFAULT NULL, PRIMARY KEY (imageId) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

spencer7593 spencer7593 · Accepted Answer · 2012-12-24T20:03:53

There is no column named sortOrder in the image table.

So, the reference to NEW.sortOrder (on the insert statement in the trigger) is invalid.

To answer your first question: No. Since there is no value supplied for that in the INSERT statement (which fires the BEFORE INSERT TRIGGER), you don't really have a source for that value.

The easy option is to provide a default value for it.

If you want to supply a value for the sortOrder column, then one option is to add a sortOrder column to the image table, and then the value can be supplied in the INSERT INTO image statement. Then it would available in the trigger.

(The purpose of the sortorder table is not at all clear.)

MySql - can BEFORE INSERT TRIGGER insert into 2 columns?

1 Answers