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.

Accepted Answer

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. ;)

MySQL BEFORE INSERT Trigger Select Into Multiple Variables?

Solution

Problem