| Subject: | On SQL Server, DBI returns num rows affected by trigger's update |
When using DBI on SQL Server to update a table which has a trigger which runs another update statement, DBI returns the count of rows affected by the trigger instead of the count of rows affected by the primary query. This occurs both with the FreeTDS ODBC driver and with the SQL Server Native Client 11.0. Meanwhile, the Java-based DBeaver 6.3.0 application is able to see the correct row-updated count.
I have no idea if this is DBI's fault or not, but here is a repeatable test case that I used both on Linux Perl 5.26.2 32-bit with DBI 1.641 & 1.643 and DBD::ODBC 1.58 and 1.61 against SQL Server 2008 (10.0.6241) via FreeTDS driver, and on Windows 64-bit Perl 5.28.1 64-bit with DBI 1.642 and DBD::ODBC 1.60 and SQL Server Native Client 11 against the same SQL Server.
my $tmp_tbl= "tmp_trig_update_example_".int(rand(1000000));
say "create table: ".$dbh->do("CREATE TABLE $tmp_tbl ( id integer identity not null, foo bit, primary key (id) )");
say "create trigger: ".$dbh->do("CREATE TRIGGER TG_${tmp_tbl}_foo ON $tmp_tbl AFTER UPDATE AS UPDATE $tmp_tbl SET foo = 0 where 1 = 0");
say "insert: ".$dbh->do("INSERT INTO $tmp_tbl (foo) VALUES (?)", {}, 1);
say "update: ".$dbh->do("UPDATE $tmp_tbl SET foo = 0 WHERE id = ?", {}, 1);
say "drop trigger: ".$dbh->do("DROP TRIGGER TG_${tmp_tbl}_foo");
say "update: ".$dbh->do("UPDATE $tmp_tbl SET foo = 0 WHERE id = ?", {}, 1);
say "drop table: ".$dbh->do("DROP TABLE $tmp_tbl");
The output is:
create table: -1
create trigger: -1
insert: 1
update: 0E0
drop trigger: -1
update: 1
drop table: -1
(the first update should also return 1, not 0E0)
and the DBI_TRACE=99 output from the first update statement looks like:
-> do for DBD::ODBC::db (DBI::db=HASH(0x35ca46c)~0x35ca394 'UPDATE tmp_trig_update_example_647401 SET foo = 0 WHERE id = ?' HASH(0x35ca79c) 1) thr#3f70a4
SQLPrepare UPDATE tmp_trig_update_example_647401 SET foo = 0 WHERE id = ?
SQLPrepare = 0
1 <- prepare= ( DBI::st=HASH(0x35ca844) ) [1 items] at DBI.pm line 1634
+dbd_st_execute_iv(35ca9f4)
dbd_st_finish(35ca9f4)
-dbd_st_execute_iv(35ca9f4)=8589934592
<- execute= ( '0E0' ) [1 items] at DBI.pm line 1635
<- rows= ( 0 ) [1 items] at DBI.pm line 1636
<> DESTROY(DBI::st=HASH(0x35ca844)) ignored for outer handle (inner DBI::st=HASH(0x35ca814) has ref cnt 1)
-> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x35ca814)~INNER) thr#3f70a4
<- DESTROY= ( undef ) [1 items] at DBI.pm line 1637
<- do= ( '0E0' ) [1 items] at dbi-update-with-trigger.t line 16
I only mainly care about the problem because it causes DBIx::Class to die on ->update() calls because it thinks it couldn't find the row for the object.