pymysql and BEFORE INSERT trigger problem

Question

I have a DB table which looks like

CREATE TABLE `localquotes` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `createTime` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `tag` varchar(8) NOT NULL,
  `monthNum` int NOT NULL,
  `flag` float NOT NULL DEFAULT '0',
  `optionType` varchar(1) NOT NULL,
  `symbol` varchar(30) NOT NULL,
  `bid` float DEFAULT NULL,
  `ask` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

for which one I have created a trigger

CREATE DEFINER=`user`@`localhost` TRIGGER `localquotes_BEFORE_INSERT` BEFORE INSERT ON `localquotes` FOR EACH ROW BEGIN

SET new.tag=left(symbol,3);
SET new.monthNum=right(left(symbol,5),1);
SET new.optionType=left(right(symbol,11),1);
SET new.flag=right(left(symbol,11),4);

END

which is causing pymysql.err.OperationalError: (1054, "Unknown column 'symbol' in 'field list'") for pymysql on simple INSERT like

insertQuery = "INSERT INTO localquotes (tag,monthNum,flag,optionType,symbol,bid) VALUES (%s,%s,%s,%s,%s,%s)"
insertValues = ('UNKNOWN', d.strftime("%m"), 0, 'X', symbol, bid)
cursor.execute(insertQuery, insertValues)
db.commit()

When I will remove that trigger insert works fine.

Any clue why code is complaining about symbol column, which exists, when there is trigger set for that column value from the insert request?

Answer 1

You must reference all the columns of the row that spawned the trigger with the NEW.* prefix.

SET new.tag=left(new.symbol,3);

And so on.