在mysql触发器中,当我对表A执行"after update“,然后使用"for each row”时,它会在每次A中行被更新时为A中的每一行运行触发器的主体,还是说将触发器应用于A中的每一行,然后如果一行被更新,它将只运行更新行的主体代码?
谢谢
发布于 2014-05-14 16:24:57
FOR EACH ROW表示获得已更新的或已删除的每个匹配行的FOR EACH ROW。
触发器主体不会遍历整个表数据,除非查询中有where条件。
下面展示了一个工作示例:
创建示例表:
drop table if exists tbl_so_q23374151;
create table tbl_so_q23374151 ( i int, v varchar(10) );
-- set test data
insert into tbl_so_q23374151
values (1,'one'),(2,'two' ),(3,'three'),(10,'ten'),(11,'eleven');
-- see current data in table**:
select * from tbl_so_q23374151;
+------+--------+
| i | v |
+------+--------+
| 1 | one |
| 2 | two |
| 3 | three |
| 10 | ten |
| 11 | eleven |
+------+--------+
5 rows in set (0.00 sec)记录触发器体中循环次数的样表:
-- let us record, loop count of trigger, in a table
drop table if exists tbl_so_q23374151_rows_affected;
create table tbl_so_q23374151_rows_affected( i int );
select count(*) as rows_affected from tbl_so_q23374151_rows_affected;
+---------------+
| rows_affected |
+---------------+
| 0 |
+---------------+Define a delete trigger:
drop trigger if exists trig_bef_del_on_tbl_so_q23374151;
delimiter //
create trigger trig_bef_del_on_tbl_so_q23374151 before delete on tbl_so_q23374151
for each row begin
set @cnt = if(@cnt is null, 1, (@cnt+1));
/* for cross checking save loop count */
insert into tbl_so_q23374151_rows_affected values ( @cnt );
end;
//
delimiter ;现在,测试操作:
delete from tbl_so_q23374151 where i like '%1%';
-- now let us see what the loop count was
select @cnt as 'cnt';
+------+
| cnt |
+------+
| 3 |
+------+现在,检查主表的触发效果:
-- now let us see the table data
select * from tbl_so_q23374151;
+------+-------+
| i | v |
+------+-------+
| 2 | two |
| 3 | three |
+------+-------+
2 rows in set (0.00 sec)
select count(*) as rows_affected from tbl_so_q23374151_rows_affected;
+---------------+
| rows_affected |
+---------------+
| 3 |
+---------------+
1 row in set (0.00 sec)发布于 2014-04-30 04:02:54
字面意思- mysql将为受SQL语句影响的每一行运行触发器代码。
而不是针对表A中的每一行本身。
发布于 2019-09-12 01:54:14
这只是一个MySQL语法怪癖,实际上是没有意义的。MySQL触发器需要使用FOR EACH ROW语法。如果没有它,你会得到一个语法错误。它们与没有FOR EACH ROW的标准SQL (例如,SQLite)触发器的工作方式完全相同。
https://stackoverflow.com/questions/23374151
复制相似问题