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: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 5.0.18 | OS: | Windows (Win98SE) |
Assigned to: | CPU Architecture: | Any |
[6 Feb 2006 1:57]
Michael Keller
[6 Feb 2006 9:08]
Valeriy Kravchuk
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.
[7 Feb 2006 7:50]
Michael Keller
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
[10 Feb 2006 13:56]
Valeriy Kravchuk
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.