MySQL Bugs: #17146: BEFOREINSERT Trigger does not recognize name of table or any of its fields

Bug #17146	BEFOREINSERT Trigger does not recognize name of table or any of its fields
Submitted:	6 Feb 2006 1:57	Modified:	10 Feb 2006 13:56
Reporter:	Michael Keller	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	Windows (Win98SE)
Assigned to:		CPU Architecture:	Any

Description:
BEFORE INSERT Trigger does not recognize name of table or any of its fields except the new.field/old.fields, when referencing the table which trigger is attached to.

How to repeat:
Use this definition for the trigger (from Navicat 7.1.10) , where: varenr, fra, til are 3 fieldnames in table alle_priser

IF (varenr=NEW.varenr) AND (til='2041-12-15') THEN CALL slut_gl_pris(NEW.fra,til); END IF

when inserting, result is Error: 1054  (ER_BAD_FIELD_ERROR) allthough, as you can see from the screen dump the fields is clearly there. I have tried using fieldname:Varenr, because I saw a bug report about case sensetivity, but the reslut was the same.

{when adding tablename i.e:
IF (alle_priser.varenr=NEW.varenr) AND (til='2041-12-15') THEN CALL slut_gl_pris(NEW.fra,til); END IF

the result is:
Error: 1109 (ER_UNKNOWN_TABLE) even though alle_priser is the name of the table itself. }

I've tried to debug and when using a default value for varenr, 
IF (5=NEW.varenr) AND (til='2041-12-15') THEN CALL slut_gl_pris(NEW.fra,til); END IF

MySQL parses through the NEW.varenr and then complains about field: til   err: 1054

Thank you for a problem report. Please, send the complete set of SQL statements that demonstrates the problem (in mysql command line client), including complete CREATE TABLE and CREATE TRIGGER statements. Then reread the manual (http://dev.mysql.com/doc/refman/5.0/en/using-triggers.html) carefully and explain, why do you think the results you got is a bug.

create database begin_trigger_bug_sample;
use begin_trigger_bug_sample;

CREATE TABLE `alle_priser` (
  `Løbenr` int(10) unsigned NOT NULL auto_increment,
  `varenr` varchar(15) NOT NULL,
  `Fra` date NOT NULL,
  `Kostpris` decimal(10,2) default NULL,
  `Avance pct` decimal(10,2) default '30.00',
  `Listepris` decimal(10,2) default NULL COMMENT 'Gælder hvis indtastet',
  `Til` date default '2041-12-15' COMMENT 'Rettes aut. når ny pris oprettes',
  PRIMARY KEY  (`Løbenr`),
  KEY `Varenr` (`varenr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `alle_priser` VALUES ('1', '13', '2006-01-23', '5.00', '100.00', null, '2006-01-23');
CREATE PROCEDURE `slut_gl_pris`(IN fra DATE, OUT til DATE) DETERMINISTIC SET til=SUBDATE (fra,1);
DELIMITER |
CREATE TRIGGER t BEFORE INSERT ON alle_priser FOR EACH ROW BEGIN IF (varenr=NEW.varenr) AND (til='2041-12-15') THEN CALL slut_gl_pris(NEW.fra,til); END IF; END;
|

-- ----------------------------
-- The following results in error
-- ----------------------------
INSERT INTO `alle_priser` VALUES ('2', '13', '2006-02-07', '5.00', '200.00', null, '2041-12-15');

I think this very simple example should work since 'varenr' clearly is a valid fieldname. If you can refer to NEW.varenr then why not simply varenr in the initial row? As I wrote in the original post, vhen I debug and use a default value i.e. '5' or '13' then MySQL parses the NEW.varenr and start complaining about fieldname 'til' which also exist.

Greetings  / Michael

Please, read that manual page I refered to:

"The OLD and NEW keywords enable you to access columns in the rows affected by a trigger. (OLD and NEW are not case sensitive.) In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name  can be used; there is no new row. In an UPDATE  trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated."

So, just add NEW. before any column of the new row you are referring to in your BEFORE INSERT trigger. It is a documented requirement, not a bug.