MySQL BEFORE INSERT Trigger Select Into Multiple Variables?

Question

I'm trying to write a trigger in MySQL and I need to set the values of several variables. The values I need to put into these variables are from a row in an existing table (not worried about multiple rows, there will only be one):

DELIMITER |

CREATE TRIGGER ins_move_to_hist BEFORE INSERT ON EndOfDay
FOR EACH ROW
BEGIN
 DECLARE _RowsMatching int;
 DECLARE _EodId  int(10) unsigned;
  DECLARE _Open  decimal(6,4);
  DECLARE _High  decimal(6,4);
  DECLARE _Low   decimal(6,4);
  DECLARE _Close  decimal(6,4);
  DECLARE _Volume  int(10) unsigned;
  DECLARE _mtime  datetime;

 SELECT _RowsMatching = Count(*), 
  EodId as _EodId, 
  Open as _Open,
  High as _High,
  Low as _Low,
  Close as _Close,
  Volume as _Volume,
  mtime as _mtime
 from EndOfDay 
 where DateId = NEW.DateId
  and TickerId = NEW.TickerId;

However, Triggers don't allow for Selecting results (which I don't need). So, when I run the create script it gives me this error:

Not allowed to return a result set from a trigger.

Answer 1

I believe you simply need to change your as statements to into statements.

UPDATE: As Bill corrected me below, the INTO statement is formatted more like the insert statement, ie; column list INTO value list.

SELECT Count(*), EodId, Open, High, Low, Close, Volume, mtime 
INTO _RowsMatching, _EodId, _Open, _High, _Low, _Close, _Volume, _mtime
...

Thanks, Bill. ;)