Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How does ‘FOR EACH ROW’ work in the MySQL trigger?
Actually ‘FOR EACH ROW’ means for each of the matched rows that get either updated or deleted. In other words, we can say that trigger is not applied to each row, it just says to execute the trigger body for each affected table row. We can illustrate this by the following example −
Example
In this example, we are creating 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 will create a trigger which triggers before deleting any value in table ‘Sample’ as follows −
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 table ‘Sample’ and the count of a number of deleted rows will be stored in @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 value of rows affected by deletion, inserted in 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)
With the help of the above example, it is clear that ‘FOR EACH ROW’ means for each of the matched rows that get either updated or deleted.
4K+ Views
