Un trigger fait-il parti d’une transaction ?

C’est une question que l’on peut se poser, même si la réponse parait évidente, nous ne sommes pas à l’abri d’éventuelles surprises. Verdict à la fin de ce post !

C’est d’ailleurs une question que l’on s’est posée, autour de la machine café, et qui du coup a fait l’objet d’un petit exercice avec Oracle, SQL Server, MySQL et Sybase… Bon, je vous rassure, nous ne faisons pas que parler bases de données pendant les pauses café mais ça arrive, parfois.

Place aux tests : Un trigger fait-il parti d’une transaction ?

Test Oracle :

SQL > create table t2 ( cnt int );
 SQL > insert into t2 values ( 0 );
 SQL > commit;
 SQL > create table t ( x int check ( x>0 ) );

SQL > create trigger t_trigger
 before insert or delete on t for each row
 begin
 if ( inserting ) then
 update t2 set cnt = cnt +1;
 else
 update t2 set cnt = cnt -1;
 end if;
 dbms_output.put_line( 'I fired and updated ' ||sql%rowcount || ' rows'  );
 end;
 /

Résultat : OUI

SQL > set serveroutput on
 SQL > insert into t values (1);
 I fired and updated 1 rows
 1 row created.
 SQL > insert into t values(-1);
 I fired and updated 1 rows
 insert into t values(-1)
 *
 ERROR at line 1:
 ORA-02290: check constraint (SYS_C009597) violated
 SQL > select * from t2;
 CNT
 ----------
 1

Test MS SQL Server :

use tempdb
 go

create table t2 ( cnt int )
 insert into  t2 values ( 0 )
 select * from t2

create table t ( x int check  ( x>0 ) )

drop trigger t_trigger
 create trigger t_trigger  on t for insert
 as
 begin
 update t2 set cnt = cnt +1
 print( 'I fired and updated ' + CAST(@@ROWCOUNT AS NVARCHAR(8))  + '  rows' )
 end

Résultat : OUI

begin tran
 insert into t values(2);
 insert into t values(-1);

(1 row(s) affected)
 I fired and updated  1 rows

(1 row(s) affected)
 Msg 547, Level 16, State  0, Line 4
 The INSERT statement conflicted with the CHECK constraint  "CK__t__x__7A5DD7C8". The conflict occurred in database "tempdb", table  "dbo.t", column 'x'.
 The statement has been terminated.

Test MySQL (InnoDB) :

Au passage, cela m’a permis de découvrir que les contraintes CHECK n’étaient pas prises en charge par MySQL : The CHECK clause is parsed but ignored by all storage engines

mysql> set autocommit=off;
 Query OK, 0 rows affected (0.02  sec)

mysql> use test
 Database changed
 mysql> create  table t2 ( cnt int ) engine=innodb;
 Query OK, 0 rows affected (0.11  sec)

mysql> insert into t2 values ( 0 );
 Query OK, 1 row  affected (0.02 sec)

mysql> commit;
 Query OK, 0 rows  affected (0.11 sec)

mysql> create table t ( x int primary key)  engine=innodb;
 Query OK, 0 rows affected (0.09 sec)

mysql>  delimiter //

mysql> create trigger t_trigger
 ->             before insert on t for each row
 ->              begin
 ->             update t2 set cnt = cnt +1;
 ->             end;
 -> //
 Query OK, 0 rows affected (0.11  sec)

mysql> delimiter ;

Résultat : OUI

mysql> insert into t values (1);
 Query OK, 1 row affected  (0.02 sec)

mysql> insert into t values (1);
 ERROR 1062  (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> select *  from t2;
 +------+
 | cnt  |
 +------+
 |    1 |
 +------+
 1 row in set (0.00 sec)

Test Sybase :

1> create trigger T1_ins_tr on T1 for insert

2> as

 3> begin

 4> select 1/0

 5> end

 6> go

Résultat : OUI

5> begin tran
 6> go
 1> insert into T1 values ('kjhkjhkjh')
 2> go
 Msg 3607, Level 16, State 0:
 Server 'ASE1502', Procedure 'T1_ins_tr', Line 4:
 Divide by zero occurred.

-----------
 1> select @@trancount
 2> go

-----------
 0

(1 row affected)

Résultat des tests : LE VERDICT !

Oui, un trigger fait bien parti d’une transaction, quel que soit le SGBD, enfin, au moins sur Oracle, SQL Serveur, MySQL et Sybase.

Je vous laisse faire vos tests sur DB2, Postgre et consorts… N’hésitez pas à nous faire un retour dans les commentaires.

A+
Cédric

Continuez votre lecture sur le blog :

• Verrous sur INSERT IGNORE en mode d’isolation par défaut (David Baffaleuf) [MySQL]
• Nouveautés MySQL 8.0 : Les Histogrammes (Capdata team) [MySQL]
• Suppression accidentelle de ligne : comment retrouver le coupable ? (David Baffaleuf) [SQL Server]
• Faciliter la lecture des mesures de temps dans performance_schema (David Baffaleuf) [MySQL]
• MySQL & Performance Schema : mais où sont passés les compteurs Com_% ?? (David Baffaleuf) [MySQL]