Cannot catch exception from MSSQL trigger when error is NOT in first trigger's statement

[michalwoz]

What steps will reproduce the problem?

Executing statement which triggers MSSQL trigger which generates error. When there is error in first trigger's query(not line) exception is caught, debug goes red - it's all OK. When there is error in any other query than first exception is NOT caught, debug goes green and silently CONTINUE request.

What is the expected result?

I expect to get framework's exception wherever error in trigger occurs not only when error is in first statement.

What do you get instead?

I get exception only when error is in first trigger's statement.

Additional info

Below code will generate NO framework's exception. But when you put QUERY_WITH_ERROR above DELETE you got exception. In all SQL clients I get exceptions from all trigger's statements, not anly from the first.

ALTER TRIGGER [dbo].[trigger_auth_item_child]
        ON [dbo].[rbac_auth_item]
        INSTEAD OF DELETE, UPDATE
        AS
        DECLARE @old_name VARCHAR (64) = (SELECT name FROM deleted)
        DECLARE @new_name VARCHAR (64) = (SELECT name FROM inserted)
        BEGIN
            BEGIN
        DELETE FROM dbo.[rbac_auth_item_child] WHERE parent IN (SELECT name FROM deleted) OR child IN (SELECT name FROM deleted);
        QUERY_WITH_ERROR
    END
        END;

Q	A
Yii version	2.0.38
PHP version	7.3.3
Operating system	Win10
DB	MSSQL

[darkdef]

Read this post - https://stackoverflow.com/questions/16003787/only-first-of-multiple-pdo-queries-being-executed Do you have the same case?

[michalwoz]

Read this post - https://stackoverflow.com/questions/16003787/only-first-of-multiple-pdo-queries-being-executed Do you have the same case?

No, this is not my case.

[darkdef]

You tried check this queries with native php_pdo?

[darkdef]

@michalwoz Please specify full code for execution query For example:

$this->getConnection()->createCommand($sql)->execute();

[michalwoz]

@darkdef The problem is about handling exceptions during use of MSSQL triggers.

To reproduce the problem you just need:

1. Create any trigger with TWO SQL queries. Ex:

   CREATE TRIGGER [dbo].[trigger_test] ON [dbo].[contractor] INSTEAD OF UPDATE AS
   BEGIN
   DELETE FROM dbo.place WHERE id = 1;  -- this query executes correctly (place with id=1 can even don't exist)
   DELETE FROM dbo.[user] WHERE id = 3; -- this query generates Integrity constraint violation exception!!! User with id=3 is used in relation with no cascades
   END;

2. Execute in Yii any query which will fire above trigger: Ex: UPDATE [contractor] SET [name]='a' WHERE [id]=1

You expect to get yii\db\IntegrityException because of second DELETE query, but you don't. Error exception is not caught. Script execution is silently stopped which results in migration success! or successful HTTP response. Finding that misleading behavior in migration took me hours. In any DB client I receive correct exception.

Let's remove first DELETE from trigger:

CREATE TRIGGER [dbo].[trigger_test] ON [dbo].[contractor] INSTEAD OF UPDATE AS
BEGIN
    DELETE FROM dbo.[user] WHERE id = 3; -- this query generates Integrity constraint violation exception!!!
END;

In this case everything works well, Integrity constraint violation yii\db\IntegrityException is caught. When "bad" SQL query is the first query executed in trigger exception handling works good.

[darkdef]

@michalwoz you trying this case without yii2, usage pdo.?

[michalwoz]

@darkdef

I've checked below code. Same results as using Yii. So this is actually not Yii bug...? Do you think this is PDO's bug or feature that I don't know about?

<?php

$dsn = "sqlsrv:server=XXX\XXXX;database=xxx";
$options = [
//    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
//    PDO::ATTR_ERRMODE            => PDO::ERRMODE_WARNING,
];
try {
    $pdo = new PDO($dsn, 'root', 'xxx', $options);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
$stmt = $pdo->prepare("UPDATE [contractor] SET [name]='a' WHERE [id]=1");
$stmt->execute();
var_dump($stmt->errorInfo());
die;

[darkdef]

I've read about this behaviour in pdo. Need to think about solutions.

[darkdef]

@michalwoz - sorry. I don't have a correct solution to this problem.

You can work with nextRowset(). Example below (on field [bar] added constrain LEN(bar) > 5):

        $sql = "INSERT INTO [foo1]([bar]) values('abcdef');
INSERT INTO [foo1]([bar]) values('abcde');";

        $this->expectException('yii\db\IntegrityException');
        $command = $connection->createCommand($sql);
        do {
            $this->assertEquals(1, $command->execute());
        } while ($command->pdoStatement->nextRowset());

I don't now how to fix this for ActiveRecord, without breaking backwards compatibility. We are working with exceptions in the internalExecute method and rowSet. Iterating whole rowser will be incorrect for select queries.

[samdark]

Closing since there's no good solution to the problem.