触发器mysql员工考勤,“对于每一行”如何在MySQL触发器中工作？

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?

Thanks

解决方案

FOR EACH ROW means for each of the matched row that gets either updated or deleted.

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:

Create sample tables:

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)

Sample table to record loop count in trigger body:

-- 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, check the trigger effect on main table:

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