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:
None 
Category:MySQL Server: Stored Routines Severity:S4 (Feature request)
Version:5.0.32, 5.0.44 OS:Linux
Assigned to: CPU Architecture:Any

[17 Sep 2007 20:09] Mack Earnhardt
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...
[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.