How does 'FOR EACH ROW' in MySQL triggers work?

Actually "FOR EACH ROW" means every matching row updated or deleted. In other words, we can say that the trigger does not apply to every row, it just says that the trigger body is executed for every affected table row. We can illustrate this with the following example -

Example

In this example, we create two tables, Sample and Sample_rowaffected, as follows-

mysql> Create table Sample(id int, value varchar(20));
Query OK, 0 rows affected (0.47 sec)

mysql> Insert into Sample(id, value) values(100, 'same'),(101,
'Different'),(500, 'excellent'),(501, 'temporary');
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> Select * from Sample;
+------+-----------+
| id   | value     |
+------+-----------+
| 100  | same      |
| 101  | Different |
| 500  | excellent |
| 501  | temporary |
+------+-----------+
4 rows in set (0.00 sec)

mysql> Create table Sample_rowaffected(id int);
Query OK, 0 rows affected (0.53 sec)

mysql> Select Count(*) as ‘Rows Affected’ from sample_rowaffected;
+---------------+
| Rows Affected |
+---------------+
|             0 |
+---------------+
1 row in set (0.10 sec)

Now, we A trigger will be created which will fire before deleting any value in the table "Sample" as shown below -

mysql> Delimiter //
mysql> Create trigger trigger_before_delete_sample BEFORE DELETE on
Sample
    -> FOR EACH ROW
    -> BEGIN
    -> SET @count = if (@count IS NULL, 1, (@count+1));
    -> INSERT INTO sample_rowaffected values (@count);
    -> END ;
    -> //
Query OK, 0 rows affected (0.15 sec)
mysql> Delimiter ;

Now the following query will delete some values ​​from the table "Sample" and delete The number of rows will be stored in the @count user variable -

mysql> Delete from Sample WHERE ID >=500;
Query OK, 2 rows affected (0.11 sec)

mysql> Select @count;
+--------+
| @count |
+--------+
|      2 |
+--------+
1 row in set (0.03 sec)

With the help of the following query we can check the values ​​of the rows affected by deletion inserted into the sample_rowaffected table as follows -

mysql> Select Count(*) as 'Rows Affected' from sample_rowaffected;
+---------------+
| Rows Affected |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)

mysql> Select * from Sample;
+------+-----------+
| id   | value     |
+------+-----------+
| 100  | same      |
| 101  | Different |
+------+-----------+
2 rows in set (0.00 sec)

in With the help of the above example, it is clear that "FOR EACH ROW" means update or delete every matching row.

The above is the detailed content of How does 'FOR EACH ROW' in MySQL triggers work?. For more information, please follow other related articles on the PHP Chinese website!