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: | |
Category: | MySQL Server: DDL | Severity: | S4 (Feature request) |
Version: | OS: | Any | |
Assigned to: | CPU Architecture: | Any |
[13 Jan 2015 2:18]
Glenn Benge
[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.