mysql delete triggers

Question

I think I read that the delete trigger doesn't know what data was deleted and loops over the whole table applying the trigger. Is that true?

Does that mean that the before delete loops over the whole table before the data is deleted and after delete loops over the whole table after the delete occurs?

Is there no way to loop over just the deleted records? So If 10 records are deleted loop over them?

DELIMITER $$
DROP TRIGGER  `before_delete_jecki_triggername`$$
CREATE TRIGGER before_delete_triggername
BEFORE DELETE ON table
FOR EACH ROW 
BEGIN
    /*do stuff*/
END$$
DELIMITER ;

Thanks,

Mat

Where did you read that? AFAIK, a `{BEFORE | AFTER} DELETE` trigger is only invoked on the records which are to be / have been deleted respectively. You can use the `OLD` table alias to access the data in the relevant record. — eggyal, Jun 28 '12 at 00:42
Someone else on SO - I am trying to find the thread. Does anyone know? — Mat Kay, Jun 28 '12 at 01:00

Ravinder Reddy · Accepted Answer · 2012-06-28T10:42:22.893

I think it was due to a confusion with FOR EACH ROW statement.
It is only for "matched records for the statement issued before trigger is invoked."

If there exists N number of records in a table and matches records for where id=x,
assuming x causes a result of less than N records, say N-5, then
FOR EACH ROW causes a loop for N-5 times only.

UPDATE:
A sample test run on the rows affected due to FOR EACH ROW statement is shown below.

mysql> -- create a test table
mysql> drop table if exists tbl; create table tbl ( i int, v varchar(10) );
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.06 sec)

mysql> -- set test data
mysql> insert into tbl values(1,'one'),(2,'two' ),(3,'three'),(10,'ten'),(11,'eleven');
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from tbl;
+------+--------+
| i    | v      |
+------+--------+
|    1 | one    |
|    2 | two    |
|    3 | three  |
|   10 | ten    |
|   11 | eleven |
+------+--------+
5 rows in set (0.02 sec)

mysql> select count(*) row_count from tbl;
+-----------+
| row_count |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)

mysql>
mysql> -- record loop count of trigger in a table
mysql> drop table if exists rows_affected; create table rows_affected( i int );
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.05 sec)

mysql> select count(*) 'rows_affected' from rows_affected;
+---------------+
| rows_affected |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql>
mysql> set @cnt=0;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> -- drop trigger if exists trig_bef_del_on_tbl;
mysql> delimiter //
mysql> create trigger trig_bef_del_on_tbl before delete on tbl
    ->   for each row begin
    ->     set @cnt = if(@cnt is null, 1, (@cnt+1));
    ->
    ->     /* for cross checking save loop count */
    ->     insert into rows_affected values ( @cnt );
    ->   end;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> -- now let us test the delete operation
mysql> delete from tbl where i like '%1%';
Query OK, 3 rows affected (0.02 sec)

mysql>
mysql> -- now let us see what the loop count was
mysql> select @cnt as 'cnt';
+------+
| cnt  |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

mysql>
mysql> -- now let us see the table data
mysql> select * from tbl;
+------+-------+
| i    | v     |
+------+-------+
|    2 | two   |
|    3 | three |
+------+-------+
2 rows in set (0.00 sec)

mysql> select count(*) row_count from tbl;
+-----------+
| row_count |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

mysql> select count(*) 'rows_affected' from rows_affected;
+---------------+
| rows_affected |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)

mysql>

Oh that makes a lot more sense. I never understood quite what the for each row was looping over. — Mat Kay, Jun 28 '12 at 01:21
So does the before delete trigger loop over all the rows in the table or does it run for each recorded that is deleted? Or does it run once for each row about to be deleted? — Mat Kay, Jun 28 '12 at 01:31
@MatKay I have included a tested example in my updated answer. — Ravinder Reddy, Jun 28 '12 at 10:43

mysql delete triggers

1 Answers1

Linked