ZF-12112: Problem with insert in a table containing an insert trigger

Description

In MS SQL Server 2008, I have created the 2 tables described on http://www.blackwasp.co.uk/SQLScopeIdentity.aspx

CREATE TABLE Sales ( SalesID INT IDENTITY(1,1) PRIMARY KEY, Item VARCHAR(50), Price MONEY )

CREATE TABLE Audit ( AuditID BIGINT IDENTITY(1000000,1) PRIMARY KEY, Item VARCHAR(50), Price MONEY )

I have set the following trigger : CREATE TRIGGER CopySalesToAudit ON Sales AFTER INSERT AS INSERT INTO Audit (Item, Price) SELECT Item, Price FROM inserted

In my ZF project I have :

Zend_Db_Table::setDefaultAdapter(Zend_registry::get('mydb')); $table = new Zend_Db_Table('Sales'); $table->insert(array('Item' => 'pc', 'Price' => 10.3));

If I run this code, the insertion and the trigger works but I get:

Zend_Db_Statement_Sqlsrv_Exception: The active result for the query contains no fields.

thrown in C:\Program Files (x86)\Zend\ZendServer\share\ZendFramework\library\Zend\Db\Statement\Sqlsrv.php on line 297 Call Stack

Zend_Db_Statement_Sqlsrv->fetchColumn()
in C:\Program Files (x86)\Zend\ZendServer\share\ZendFramework\library\Zend\Db\Adapter\Sqlsrv.php on line 385
Zend_Db_Adapter_Sqlsrv->insert()
in C:\www\intranet-iae\application\library\iae\Sqlsrv.php on line 141
IAE_Sqlsrv->insert()
in C:\Program Files (x86)\Zend\ZendServer\share\ZendFramework\library\Zend\Db\Table\Abstract.php on line 1073
Zend_Db_Table_Abstract->insert()
in C:\www\intranet-iae\application\modules\default\controllers\GrhController.php on line 23

If I remove the trigger, this code works properly. The remaining part of the article pointed at the beginning of this message might give a clue about this problem.

Comments

The workaround I found to this problem consists in subclassing Zend_Db_Adapter_Sqlsrv and:

  1. removing $this->_lastInsertSQL at the end of the SQL statement in Zend_Db_Adapter_Sqlsrv::insert()
  2. replacing:

$stmt->nextRowset(); $this->_lastInsertId = $stmt->fetchColumn();

with :

$this->_lastInsertId = $this->lastInsertId($table);