| Bug #10902 | 'After' Trigger not fired if 'before' trigger errors out | ||
|---|---|---|---|
| Submitted: | 27 May 2005 5:54 | Modified: | 8 Jul 2005 22:09 |
| Reporter: | Omer Barnir (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | OS: | Linux (Linux Suse 9.3) | |
| Assigned to: | Dmitry Lenev | CPU Architecture: | Any |
[27 May 2005 5:56]
Omer Barnir
Typo in description: Should read ... and an 'on-delete' after trigger... (not 'undelete')
[1 Jun 2005 10:48]
Dmitry Lenev
Hi, Omer!
The real bug here is that 'before' trigger failure is not preventing delete
operation to happen. But this was fixed in 5.0.7, now the following will
happen:
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t1 (f1 text, f2 integer);
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> create trigger del_trig_before BEFORE DELETE
-> on t1 for each row
-> select count(*) into @test_before from t1
-> where old.f1 = 'Trigger Test';
Query OK, 0 rows affected (0.00 sec)
mysql> Create trigger del_trig_after AFTER DELETE
-> on t1 for each row set @test_after = 10 ;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> set @test_after=0;
Query OK, 0 rows affected (0.00 sec)
mysql> Insert into t1 (f1, f2) values ('Trigger Test', 1) ;
Query OK, 1 row affected (0.01 sec)
mysql> Insert into t1 (f1, f2) values ('Trigger Test', 2) ;
Query OK, 1 row affected (0.01 sec)
mysql> select @test_after;
+-------------+
| @test_after |
+-------------+
| 0 |
+-------------+
1 row in set (0.01 sec)
mysql> select f1, f2 from t1 where f1 = 'Trigger Test';
+--------------+------+
| f1 | f2 |
+--------------+------+
| Trigger Test | 1 |
| Trigger Test | 2 |
+--------------+------+
2 rows in set (0.00 sec)
mysql> Delete from t1 where f2 = 2;
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES
mysql> show errors;
+-------+------+--------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------+
| Error | 1100 | Table 't1' was not locked with LOCK TABLES |
+-------+------+--------------------------------------------+
1 row in set (0.01 sec)
mysql> select f1, f2 from t1 where f1 = 'Trigger Test';
+--------------+------+
| f1 | f2 |
+--------------+------+
| Trigger Test | 1 |
| Trigger Test | 2 |
+--------------+------+
2 rows in set (0.00 sec)
mysql> select @test_after;
+-------------+
| @test_after |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
As you see 'before' trigger failure prevents row from being deleted and causes whole delete statement to fail, and indeed 'after' trigger is not invoked in this case...
Our documentation should be updated with following information:
- Error during 'before' or 'after' trigger execution will result in failure of the whole statement that caused invocation of this trigger.
- If 'before' trigger fail then impending operation on the row won't be executed
- 'after' trigger will be only executed if both 'before' trigger and corresponding operation on the row will be executed successfully (the latter for example may fail because of primary key constraint)
- Failure of trigger and thus of the whole statement for transactional table should cause rollback of all changes done by this statement. For non-transactional tables such rollback
won't be executed so in case when we have error during 'after' trigger execution or during execution of 'before' trigger in case when some changes to table were already made statement will fail but still will cause some effect.
[8 Jul 2005 22:09]
Paul DuBois
Noted bugfix in 5.0.7 changelog. Added Dmitri's error-handling information to: http://dev.mysql.com/doc/mysql/en/using-triggers.html
Description: If an 'on delete before' and an 'undelete' after triggers are both defined on a table, and the 'before' trigger errors out, the 'after' trigger is not fired either. How to repeat: use test; drop table if exists t1; create table t1 (f1 text, f2 integer); create trigger del_trig_before BEFORE DELETE on t1 for each row select count(*) into @test_before from t1 where old.f1 = 'Trigger Test'; Create trigger del_trig_after AFTER DELETE on t1 for each row set @test_after = 10 ; set @test_after=0; Insert into t1 (f1, f2) values ('Trigger Test', 1) ; Insert into t1 (f1, f2) values ('Trigger Test', 2) ; select @test_after; select f1, f2 from t1 where f1 = 'Trigger Test'; Delete from t1 where f2 = 2; show errors; select f1, f2 from t1 where f1 = 'Trigger Test'; select @test_after; drop trigger t1.del_trig_before; delete from t1 where f2 = 1; select f1, f2 from t1 where f1 = 'Trigger Test'; select @test_after; Detailed Steps: ========= Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 to server version: 5.0.6-beta-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test; Database changed mysql> drop table if exists t1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> create table t1 (f1 text, f2 integer); Query OK, 0 rows affected (0.01 sec) mysql> mysql> create trigger del_trig_before BEFORE DELETE on t1 for each row -> select count(*) into @test_before from t1 -> where old.f1 = 'Trigger Test'; Query OK, 0 rows affected (0.00 sec) mysql> Create trigger del_trig_after AFTER DELETE on t1 for each row set @test_after = 10 ; Query OK, 0 rows affected (0.00 sec) mysql> mysql> set @test_after=0; Query OK, 0 rows affected (0.00 sec) mysql> Insert into t1 (f1, f2) values ('Trigger Test', 1) ; Query OK, 1 row affected (0.00 sec) mysql> Insert into t1 (f1, f2) values ('Trigger Test', 2) ; Query OK, 1 row affected (0.00 sec) mysql> select @test_after; +-------------+ | @test_after | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec) mysql> select f1, f2 from t1 where f1 = 'Trigger Test'; +--------------+------+ | f1 | f2 | +--------------+------+ | Trigger Test | 1 | | Trigger Test | 2 | +--------------+------+ 2 rows in set (0.00 sec) mysql> Delete from t1 where f2 = 2; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> show errors; +-------+------+--------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------+ | Error | 1100 | Table 't1' was not locked with LOCK TABLES | +-------+------+--------------------------------------------+ 1 row in set (0.00 sec) mysql> select f1, f2 from t1 where f1 = 'Trigger Test'; +--------------+------+ | f1 | f2 | +--------------+------+ | Trigger Test | 1 | +--------------+------+ 1 row in set (0.00 sec) mysql> select @test_after; +-------------+ | @test_after | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec) ###Note:> If the 'after' trigger had been fired, the above value should have been 10 mysql> mysql> drop trigger t1.del_trig_before; Query OK, 0 rows affected (0.01 sec) mysql> delete from t1 where f2 = 1; Query OK, 1 row affected (0.00 sec) mysql> select f1, f2 from t1 where f1 = 'Trigger Test'; Empty set (0.00 sec) mysql> select @test_after; +-------------+ | @test_after | +-------------+ | 10 | +-------------+ 1 row in set (0.00 sec) mysql> ###Note:> After the 'before' trigger is removed and another row is deleted the after trigger is fired as expected (@test_after=10)