Verify identity return value on inserting to a trigger table. MSSQL's OUTPUT INSERTED clause does not work for the case of a table having an identity (autoincrement) primary key column, and which also has a trigger configured to fire upon each insert and sub
(self, metadata, connection)
| 159 | __backend__ = True |
| 160 | |
| 161 | def test_fetchid_trigger(self, metadata, connection): |
| 162 | # TODO: investigate test hang on mssql when connection fixture is used |
| 163 | """ |
| 164 | Verify identity return value on inserting to a trigger table. |
| 165 | |
| 166 | MSSQL's OUTPUT INSERTED clause does not work for the |
| 167 | case of a table having an identity (autoincrement) |
| 168 | primary key column, and which also has a trigger configured |
| 169 | to fire upon each insert and subsequently perform an |
| 170 | insert into a different table. |
| 171 | |
| 172 | SQLALchemy's MSSQL dialect by default will attempt to |
| 173 | use an OUTPUT_INSERTED clause, which in this case will |
| 174 | raise the following error: |
| 175 | |
| 176 | ProgrammingError: (ProgrammingError) ('42000', 334, |
| 177 | "[Microsoft][SQL Server Native Client 10.0][SQL Server]The |
| 178 | target table 't1' of the DML statement cannot have any enabled |
| 179 | triggers if the statement contains an OUTPUT clause without |
| 180 | INTO clause.", 7748) 'INSERT INTO t1 (descr) OUTPUT inserted.id |
| 181 | VALUES (?)' ('hello',) |
| 182 | |
| 183 | This test verifies a workaround, which is to rely on the |
| 184 | older SCOPE_IDENTITY() call, which still works for this scenario. |
| 185 | To enable the workaround, the Table must be instantiated |
| 186 | with the init parameter 'implicit_returning = False'. |
| 187 | |
| 188 | Note: this **wont work** with SELECT @@identity. it has to be |
| 189 | SCOPE_IDENTITY(). for pyodbc, this means the function has to be |
| 190 | embedded on the same line as the INSERT. |
| 191 | |
| 192 | """ |
| 193 | |
| 194 | # TODO: this same test needs to be tried in a multithreaded context |
| 195 | # with multiple threads inserting to the same table. |
| 196 | # TODO: check whether this error also occurs with clients other |
| 197 | # than the SQL Server Native Client. Maybe an assert_raises |
| 198 | # test should be written. |
| 199 | meta = metadata |
| 200 | t1 = Table( |
| 201 | "t1", |
| 202 | meta, |
| 203 | Column("id", Integer, Identity(start=100), primary_key=True), |
| 204 | Column("descr", String(200)), |
| 205 | # the following flag will prevent the |
| 206 | # MSSQLCompiler.returning_clause from getting called, |
| 207 | # though the ExecutionContext will still have a |
| 208 | # _select_lastrowid, so the SELECT SCOPE_IDENTITY() will |
| 209 | # hopefully be called instead. |
| 210 | implicit_returning=False, |
| 211 | ) |
| 212 | t2 = Table( |
| 213 | "t2", |
| 214 | meta, |
| 215 | Column("id", Integer, Identity(start=200), primary_key=True), |
| 216 | Column("descr", String(200)), |
| 217 | ) |
| 218 |