SQL Server 2005, SQL Driver for PHP v1.1 eats "Transaction doomed in trigger" errors

Question

short version: the sqlsrv driver (Native Client wrapper) "eats" constraint violation errors generated from triggers; the mssql driver (ntwdlib wrapper) reports them just fine.

• SQL Server 2005
• PHP 5.3.1
• SQL Server Driver for PHP 1.1

fixture:

CREATE TABLE t (
  t INT NOT NULL PRIMARY KEY
);
CREATE VIEW v AS
  SELECT CURRENT_TIMESTAMP AS v
;
CREATE TRIGGER vt ON v
INSTEAD OF INSERT
AS BEGIN
BEGIN TRY
  INSERT INTO t SELECT 1 UNION ALL SELECT 1;
END TRY
BEGIN CATCH
  RAISERROR('fubar!', 17, 0);
END CATCH
END;

running INSERT INTO v SELECT CURRENT_TIMESTAMP; through Management Studio yields

(0 row(s) affected)
Msg 3616, Level 16, State 1, Line 1
Transaction doomed in trigger. Batch has been aborted.
Msg 50000, Level 17, State 0, Procedure vt, Line 8
fubar!

no error is reported when I run it through sqlsrv_query:

$conn = sqlsrv_connect(...);
var_dump(sqlsrv_query($conn, 'INSERT INTO v SELECT CURRENT_TIMESTAMP'));
var_dump(sqlsrv_errors());

outputs

resource(11) of type (SQL Server Statement)
NULL

the application has (it seems) no way to find out that the trigger failed other than through later statements failing.

The question: What's up? Do you use this PHP driver? Do you use views with DML triggers? Does the driver report doomed transactions?

edit 2010-02-17 11:50: the first version of the question incorrectly claimed that I saw the artifact with the trigger containing a simple INSERT. well, it only happens when the constraint-violating DML is inside a TRY block. sorry for the confusion.

edit 2010-03-03: just so that you guys don't get too attached to the severity level in RAISERROR, the real code tries to rethrow the caught error with ERROR_NUMBER, ERROR_SEVERITY and ERROR_STATE.

furthermore, please pay attention to the questions asked:

The question: What's up? Do you use this PHP driver? Do you use views with DML triggers? Does the driver report doomed transactions?

please don't try to harvest the bounty without having firsthand experience with the situation described here.

Answer 1

I have run into this problem in the past, it is not just PHP that intricacy. For some reason, that I can't figure out and find in any documentation, you need the specify the severity to it's max of 18 for non-sysadmins. Try this:

CREATE TABLE t (
  t INT NOT NULL PRIMARY KEY
);
CREATE VIEW v AS
  SELECT CURRENT_TIMESTAMP AS v
;
CREATE TRIGGER vt ON v
INSTEAD OF INSERT
AS BEGIN
BEGIN TRY
  INSERT INTO t SELECT 1 UNION ALL SELECT 1;
END TRY
BEGIN CATCH
  RAISERROR('fubar!', 18, 0);
END CATCH
END;

note: I have only changed the severity from 17 to 18 in the code above.

Answer 2

A Severity Level of 17 indicates "Insufficient Resources". Try using 16 instead. (Error Message Severity Levels)

From the canonical reference: Error Handling in SQL 2000 – a Background

Severity level – a number from 0 to 25. The stort story is that if the severity level is in the range 0-10, the message is informational or a warning, and not an error. Errors resulting from programming errors in your SQL code have a severity level in the range 11-16. Severity levels 17-25 indicate resource problems, hardware problems or internal problems in SQL Server, and if the severity is 20 or higher, the connection is terminated. For the long story, see the section More on Severity Levels for some interesting tidbits. For system messages you can find the severity level in master..sysmessages, but for some messages SQL Server employs a different severity level than what's in sysmessages.

Also see: Error Handling in SQL 2005 and Later

Answer 3

Have you tried a severity of 10 or below? By the way I have always had good luck with the SQL Driver and PHP. On 2005 and 2008. If this doesn't work, try a different server to make sure it isn't your server config.