Bug #75484 triggers - use NEW or OLD data as table references
Submitted: 13 Jan 2015 2:18 Modified: 19 Jan 2015 18:35
Reporter: Glenn Benge Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[13 Jan 2015 2:18] Glenn Benge
Description:
when creating triggers ...

why are we forced to process NEWly inserted rows one at a time using the FOR EACH ROW mechanism?

MySQL is a relational database - "relation" refers to a group of data, why are we forced to parse NEW data, one row at a time, as if it was a flat sequential file.

We should have the choice to utilise NEW or OLD as a table in a SELECT statement.

How to repeat:

current syntax requires

CREATE TRIGGER triggerName
AFTER INSERT ON tableName1
FOR EACH ROW
 insert into tablename2 (t2col1, t2col2, t2col3)
 (select NEW.t1col1, NEW.t1col2, NEW.t1col3);

Suggested fix:
we should be able to process the NEW data as a table :

CREATE TRIGGER triggerName
AFTER INSERT ON tableName1
insert into tablename2 (t2col1, t2col2, t2col3)
(select t1col1, t1col2, t1col3 from NEW);

the above example doesn't really provide additional functionality, but .... it becomes particularly useful with aggregation. Aggregation is not possible using the current syntax - you can't currently aggregate NEW data if you can only access the data one row at a time.

CREATE TRIGGER triggerName
AFTER INSERT ON tableName1
insert into tablenameSumm (sumID, sumValue)
(select t1ID, sum(t1Value) from NEW, group by t1ID);

and similarly for OLD data
[13 Jan 2015 2:20] Glenn Benge
changed category from "MySQL Server: DML" to "MySQL Server: DDL"
[19 Jan 2015 16:19] MySQL Verification Team
FOR EACH ROW is required by SQL standard. In a trigger you can use both NEW and OLD data from the table. OLD data are, of course, read-only.
[19 Jan 2015 18:32] Glenn Benge
you've missed my point.

because FOR EACH ROW is compulsory, it leaves no option but to process the NEW (or OLD) data one row at a time.

The TRIGGER syntax should allow for:

select col1, col2, col3 from NEW.

or more realistically

insert into tableJohn (John1, John2, John3, John4,)
(select NEW.col1, NEW.col2, tableFred.fred1, tableFred.fred2
from NEW
inner join tableFred on NEW.col0 = tableFred.col0
where NEW.col4 = 'xx')

but this syntax is not allowable because the FOR EACH ROW forces us to use the NEW data one row at a time, rather than processing it as a relational set.
[19 Jan 2015 18:35] Glenn Benge
re-opening because the response from Sinisa did not address the reported issue.