{"id":430,"date":"2010-03-30T14:31:54","date_gmt":"2010-03-30T13:31:54","guid":{"rendered":"http:\/\/blog.capdata.fr\/?p=430"},"modified":"2019-09-13T14:58:52","modified_gmt":"2019-09-13T13:58:52","slug":"un-trigger-fait-il-parti-dune-transaction","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/","title":{"rendered":"Un trigger fait-il parti d’une transaction ?"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

C’est une question que l’on peut se poser, m\u00eame si la r\u00e9ponse parait \u00e9vidente, nous ne sommes pas \u00e0 l’abri d’\u00e9ventuelles surprises. Verdict \u00e0 la fin de ce post !<\/p>\n

C’est d’ailleurs une question que l’on s’est pos\u00e9e, autour de la machine caf\u00e9, 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\u00e9es pendant les pauses caf\u00e9 mais \u00e7a arrive, parfois.<\/p>\n

Place aux tests : Un trigger fait-il parti d’une transaction ?<\/h2>\n

Test Oracle :<\/span><\/h3>\n
SQL > create table t2 ( cnt int );\r\n SQL > insert into t2 values ( 0 );\r\n SQL > commit;\r\n SQL > create table t ( x int check ( x>0 ) );<\/pre>\n
SQL > create trigger t_trigger\r\n before insert or delete on t for each row\r\n begin\r\n if ( inserting ) then\r\n update t2 set cnt = cnt +1;\r\n else\r\n update t2 set cnt = cnt -1;\r\n end if;\r\n dbms_output.put_line( 'I fired and updated ' ||sql%rowcount || ' rows'  );\r\n end;\r\n \/<\/pre>\n
R\u00e9sultat : OUI
\n<\/span><\/p>\n
SQL > set serveroutput on\r\n SQL > insert into t values (1);\r\n I fired and updated 1 rows\r\n 1 row created.\r\n SQL > insert into t values(-1);\r\n I fired and updated 1 rows\r\n insert into t values(-1)\r\n *\r\n ERROR at line 1:\r\n ORA-02290: check constraint (SYS_C009597) violated\r\n SQL > select * from t2;\r\n CNT\r\n ----------\r\n 1<\/pre>\n
Test MS SQL Server :<\/span><\/h3>\n
use tempdb\r\n go<\/pre>\n
create table t2 ( cnt int )\r\n insert into  t2 values ( 0 )\r\n select * from t2<\/pre>\n
create table t ( x int check  ( x>0 ) )<\/pre>\n
drop trigger t_trigger\r\n create trigger t_trigger  on t for insert\r\n as\r\n begin\r\n update t2 set cnt = cnt +1\r\n print( 'I fired and updated ' + CAST(@@ROWCOUNT AS NVARCHAR(8))\u00a0 + '  rows' )\r\n end<\/pre>\n
R\u00e9sultat : OUI
\n<\/span><\/p>\n
begin tran\r\n insert into t values(2);\r\n insert into t values(-1);<\/pre>\n
(1 row(s) affected)\r\n I fired and updated  1 rows<\/pre>\n
(1 row(s) affected)\r\n Msg 547, Level 16, State  0, Line 4\r\n The INSERT statement conflicted with the CHECK constraint  \"CK__t__x__7A5DD7C8\". The conflict occurred in database \"tempdb\", table  \"dbo.t\", column 'x'.\r\n The statement has been terminated.<\/pre>\n
Test MySQL (InnoDB) :<\/span><\/h3>\n
Au passage, cela m’a permis de d\u00e9couvrir que les contraintes CHECK <\/em>n’\u00e9taient  pas prises en charge par MySQL : The CHECK<\/code> clause is  parsed but ignored by all storage engines<\/em><\/p>\n
mysql> set autocommit=off;\r\n Query OK, 0 rows affected (0.02  sec)<\/pre>\n
mysql> use test\r\n Database changed\r\n mysql> create  table t2 ( cnt int ) engine=innodb;\r\n Query OK, 0 rows affected (0.11  sec)<\/pre>\n
mysql> insert into t2 values ( 0 );\r\n Query OK, 1 row  affected (0.02 sec)<\/pre>\n
mysql> commit;\r\n Query OK, 0 rows  affected (0.11 sec)<\/pre>\n
mysql> create table t ( x int primary key)  engine=innodb;\r\n Query OK, 0 rows affected (0.09 sec)<\/pre>\n
mysql>  delimiter \/\/<\/pre>\n
mysql> create trigger t_trigger\r\n ->\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 before insert on t for each row\r\n ->\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0  begin\r\n ->\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 update t2 set cnt = cnt +1;\r\n ->\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 end;\r\n -> \/\/\r\n Query OK, 0 rows affected (0.11  sec)<\/pre>\n
mysql> delimiter ;<\/pre>\n
R\u00e9sultat : OUI
\n<\/span><\/p>\n
mysql> insert into t values (1);\r\n Query OK, 1 row affected  (0.02 sec)<\/pre>\n
mysql> insert into t values (1);\r\n ERROR 1062  (23000): Duplicate entry '1' for key 'PRIMARY'<\/pre>\n
mysql> select *  from t2;\r\n +------+\r\n | cnt\u00a0 |\r\n +------+\r\n |\u00a0\u00a0\u00a0 1 |\r\n +------+\r\n 1 row in set (0.00 sec)<\/pre>\n
Test Sybase :
\n<\/span><\/h3>\n
1> create trigger T1_ins_tr on T1 for insert<\/pre>\n
2> as<\/pre>\n
 3> begin<\/pre>\n
 4> select 1\/0<\/pre>\n
 5> end<\/pre>\n
 6> go<\/pre>\n
R\u00e9sultat : OUI
\n<\/span><\/p>\n
5> begin tran\r\n 6> go\r\n 1> insert into T1 values ('kjhkjhkjh')\r\n 2> go\r\n Msg 3607, Level 16, State 0:\r\n Server 'ASE1502', Procedure 'T1_ins_tr', Line 4:\r\n Divide by zero occurred.<\/pre>\n
-----------\r\n 1> select @@trancount\r\n 2> go<\/pre>\n
-----------\r\n 0<\/pre>\n
(1 row affected)<\/pre>\n
R\u00e9sultat des tests : LE VERDICT !<\/h2>\n
Oui, un trigger fait bien parti d’une transaction, quel que soit le SGBD, enfin, au moins sur Oracle, SQL Serveur, MySQL et Sybase.<\/p>\n
Je vous laisse faire vos tests sur DB2, Postgre et consorts… N’h\u00e9sitez pas \u00e0 nous faire un retour dans les commentaires.<\/p>\n
A+
\nC\u00e9dric<\/p>\n\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>","protected":false},"excerpt":{"rendered":"
C’est une question que l’on peut se poser, m\u00eame si la r\u00e9ponse parait \u00e9vidente, nous ne sommes pas \u00e0 l’abri d’\u00e9ventuelles surprises. Verdict \u00e0 la fin de ce post ! C’est d’ailleurs une question que l’on s’est pos\u00e9e, autour de… Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":14,"featured_media":7955,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[143,4,3,5,6],"tags":[67,66,65,64],"class_list":["post-430","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-general","category-mysql","category-oracle","category-sqlserver","category-sybase","tag-acid","tag-atomique","tag-transaction","tag-trigger"],"yoast_head":"\nUn trigger fait-il parti d'une transaction ? - Capdata TECH BLOG<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Un trigger fait-il parti d'une transaction ? - Capdata TECH BLOG\" \/>\n<meta property=\"og:description\" content=\"C’est une question que l’on peut se poser, m\u00eame si la r\u00e9ponse parait \u00e9vidente, nous ne sommes pas \u00e0 l’abri d’\u00e9ventuelles surprises. Verdict \u00e0 la fin de ce post ! C’est d’ailleurs une question que l’on s’est pos\u00e9e, autour de… Continuer la lecture →\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/\" \/>\n<meta property=\"og:site_name\" content=\"Capdata TECH BLOG\" \/>\n<meta property=\"article:published_time\" content=\"2010-03-30T13:31:54+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-09-13T13:58:52+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2010\/03\/trigger.png\" \/>\n\t<meta property=\"og:image:width\" content=\"659\" \/>\n\t<meta property=\"og:image:height\" content=\"369\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Benjamin VESAN\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"Benjamin VESAN\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/\"},\"author\":{\"name\":\"Benjamin VESAN\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/ae56d1d3d5680d95ccc2c927e44bdc3e\"},\"headline\":\"Un trigger fait-il parti d’une transaction ?\",\"datePublished\":\"2010-03-30T13:31:54+00:00\",\"dateModified\":\"2019-09-13T13:58:52+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/\"},\"wordCount\":204,\"commentCount\":1,\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"keywords\":[\"ACID\",\"atomique\",\"transaction\",\"trigger\"],\"articleSection\":[\"G\u00e9n\u00e9ral\",\"MySQL\",\"Oracle\",\"SQL Server\",\"Sybase\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/\",\"name\":\"Un trigger fait-il parti d'une transaction ? - Capdata TECH BLOG\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/#website\"},\"datePublished\":\"2010-03-30T13:31:54+00:00\",\"dateModified\":\"2019-09-13T13:58:52+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/blog.capdata.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Un trigger fait-il parti d’une transaction ?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.capdata.fr\/#website\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"name\":\"Capdata TECH BLOG\",\"description\":\"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting\",\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.capdata.fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/blog.capdata.fr\/#organization\",\"name\":\"Capdata TECH BLOG\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"contentUrl\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"width\":800,\"height\":254,\"caption\":\"Capdata TECH BLOG\"},\"image\":{\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/ae56d1d3d5680d95ccc2c927e44bdc3e\",\"name\":\"Benjamin VESAN\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/author\/bvesan\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Un trigger fait-il parti d'une transaction ? - Capdata TECH BLOG","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/","og_locale":"fr_FR","og_type":"article","og_title":"Un trigger fait-il parti d'une transaction ? - Capdata TECH BLOG","og_description":"C’est une question que l’on peut se poser, m\u00eame si la r\u00e9ponse parait \u00e9vidente, nous ne sommes pas \u00e0 l’abri d’\u00e9ventuelles surprises. Verdict \u00e0 la fin de ce post ! C’est d’ailleurs une question que l’on s’est pos\u00e9e, autour de… Continuer la lecture →","og_url":"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/","og_site_name":"Capdata TECH BLOG","article_published_time":"2010-03-30T13:31:54+00:00","article_modified_time":"2019-09-13T13:58:52+00:00","og_image":[{"width":659,"height":369,"url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2010\/03\/trigger.png","type":"image\/png"}],"author":"Benjamin VESAN","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"Benjamin VESAN","Dur\u00e9e de lecture estim\u00e9e":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/#article","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/"},"author":{"name":"Benjamin VESAN","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/ae56d1d3d5680d95ccc2c927e44bdc3e"},"headline":"Un trigger fait-il parti d’une transaction ?","datePublished":"2010-03-30T13:31:54+00:00","dateModified":"2019-09-13T13:58:52+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/"},"wordCount":204,"commentCount":1,"publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"keywords":["ACID","atomique","transaction","trigger"],"articleSection":["G\u00e9n\u00e9ral","MySQL","Oracle","SQL Server","Sybase"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/","url":"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/","name":"Un trigger fait-il parti d'une transaction ? - Capdata TECH BLOG","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/#website"},"datePublished":"2010-03-30T13:31:54+00:00","dateModified":"2019-09-13T13:58:52+00:00","breadcrumb":{"@id":"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.capdata.fr\/index.php\/un-trigger-fait-il-parti-dune-transaction\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/blog.capdata.fr\/"},{"@type":"ListItem","position":2,"name":"Un trigger fait-il parti d’une transaction ?"}]},{"@type":"WebSite","@id":"https:\/\/blog.capdata.fr\/#website","url":"https:\/\/blog.capdata.fr\/","name":"Capdata TECH BLOG","description":"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting","publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.capdata.fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/blog.capdata.fr\/#organization","name":"Capdata TECH BLOG","url":"https:\/\/blog.capdata.fr\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/","url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","contentUrl":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","width":800,"height":254,"caption":"Capdata TECH BLOG"},"image":{"@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/"]},{"@type":"Person","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/ae56d1d3d5680d95ccc2c927e44bdc3e","name":"Benjamin VESAN","url":"https:\/\/blog.capdata.fr\/index.php\/author\/bvesan\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/430","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/users\/14"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/comments?post=430"}],"version-history":[{"count":10,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/430\/revisions"}],"predecessor-version":[{"id":884,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/430\/revisions\/884"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media\/7955"}],"wp:attachment":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media?parent=430"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/categories?post=430"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/tags?post=430"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}