How does "for each row" work in triggers in mysql?

Question

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

Answer 1

It's just a MySQL syntax quirk and it's virtually meaningless. MySQL triggers require the FOR EACH ROW syntax. You'll get a syntax error without it. They work exactly the same as Standard SQL (e.g., SQLite) triggers without FOR EACH ROW.

https://dev.mysql.com/doc/refman/8.0/en/create-trigger.html

Answer 2

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)