mysql删除触发器 [英] mysql delete triggers

问题描述

我想我读到删除触发器不知道删除了什么数据，并在整个表上循环使用该触发器。是真的吗？

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?

这是否意味着在删除数据之前，before删除会在整个表上循环，而在删除发生之后，delete会在整个表上循环吗？

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?

是否没有办法仅循环删除的记录？因此，如果删除了10条记录，那么循环遍历它们？

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 ;

谢谢

垫子

推荐答案

我认为这是由于与 FOR EACH ROW 语句的混淆。 br>
仅适用于 在触发器被触发之前发出的语句的匹配记录。

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."

如果存在 N 表中的记录数，并匹配的记录，其中id = x ，

假定 x 导致结果少于 N 条记录，例如 N-5 ，然后

每行只会导致循环一次 N-5 次。

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.

更新：

对由于 FOR EACH ROW而受影响的行进行了示例测试语句如下所示。

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>

这篇关于mysql删除触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！