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
– eggyal, Commented Jun 28, 2012 at 0:42
Someone else on SO - I am trying to find the thread. Does anyone know? — Mat Kay
– Mat Kay, Commented Jun 28, 2012 at 1:00

Ravinder Reddy · Accepted Answer · 2012-06-28 10:42:22Z

2

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>

edited Jun 28, 2012 at 10:42

answered Jun 28, 2012 at 1:17

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mat Kay Over a year ago

Oh that makes a lot more sense. I never understood quite what the for each row was looping over.

Mat Kay Over a year ago

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?

Ravinder Reddy Over a year ago

@MatKay I have included a tested example in my updated answer.

Collectives™ on Stack Overflow

mysql delete triggers

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related