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
Triage: Triaged: D5 (Feature request)

[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.