Bug #31068 "select into" syntax doesn't allow new.* variables as target
| Submitted: | 17 Sep 2007 20:09 | Modified: | 18 Sep 2007 4:43 |
|---|---|---|---|
| Reporter: | Mack Earnhardt | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S4 (Feature request) |
| Version: | 5.0.32, 5.0.44 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any |
[18 Sep 2007 4:43]
Valeriy Kravchuk
Thank you for a prblem report. I was able to repeat the behaviour described, but I can not call it a bug, as manual clearly says (http://dev.mysql.com/doc/refman/5.0/en/using-triggers.html): "A column named with NEW can be referred to if you have the SELECT privilege for it. In a BEFORE trigger, you can also change its value with SET NEW.col_name = value if you have the UPDATE privilege for it." Looks like SET is the only intended way to change NEW.col values. I agree, though, that SELECT INTO should be also supported. So, I think, this is a reasonable feature request.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.048 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: The following syntax results in ERROR 1327 (42000): Undeclared variable: new create trigger messages_insert before insert on messages for each row select sm.id into new.shared_message_id from shared_messages sm where sm.mail_id = new.mail_id; Here is my workaround: create trigger messages_insert before insert on messages for each row begin declare temp_id int; select sm.id into temp_id from shared_messages sm where sm.mail_id = new.mail_id; set new.shared_message_id = temp_id; end; How to repeat: See example above...