如何“针对每一行"在MySQL触发器中工作? [英] How does "for each row" work in triggers in mysql?
问题描述
在mysql触发器中,当我在表A
上执行更新后",然后使用针对每一行"时,每次更新行时,它将为A
中的每一行运行触发器的主体在A
中,还是说将触发器应用于A
中的每一行,然后如果一行被更新,它将仅运行该更新行的主体代码?
In mysql triggers, when I do a "after update" on table A
and then use "for each row", will it run the body of the trigger for each row in A
every time a row gets updated in A
, or is it saying to apply the trigger to every row in A
and then if a row gets updated, it will only run the body code for that updated row only?
谢谢
推荐答案
FOR EACH ROW
表示每个匹配行的 ,它可以 更新 或 删除 .
FOR EACH ROW
means for each of the matched row that gets either updated or deleted.
除非查询中存在where条件,否则触发主体将不会遍历整个表数据.
Trigger body won't loop through the entire table data unless there is a where condition in the query.
一个工作示例如下所示:
A working example is demonstrated below:
创建示例表:
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 ;
现在,测试删除操作:
Now, test a delete operation:
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)
这篇关于如何“针对每一行"在MySQL触发器中工作?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!