SQL Server triggers and sqlalchemy interference problem. Help needed

Question

I need to have a kind of 'versioning' for some critical tables, and tried to implement it in a rather simple way:

CREATE TABLE [dbo].[Address] (
  [id] bigint IDENTITY(1, 1) NOT NULL,
  [post_code] bigint NULL,
...
)

CREATE TABLE [dbo].[Address_History] (
  [id] bigint NOT NULL,
  [id_revision] bigint NOT NULL,
  [post_code] bigint NULL,
...
  CONSTRAINT [PK_Address_History] PRIMARY KEY CLUSTERED ([id], [id_revision]),
  CONSTRAINT [FK_Address_History_Address]...
  CONSTRAINT [FK_Address_History_Revision]...
)

CREATE TABLE [dbo].[Revision] (
  [id] bigint IDENTITY(1, 1) NOT NULL,
  [id_revision_operation] bigint NULL,
  [id_document_info] bigint NULL,
  [description] varchar(255) COLLATE Cyrillic_General_CI_AS NULL,
  [date_revision] datetime NULL,
...
)

and a bunch of triggers on insert/update/delete for each table, that is intended to store it's changes.

My application is based on PyQt + sqlalchemy, and when I try to insert an entity, that is stored in a versioned table, sqlalchemy fires an error:

The target table 'Heritage' of the DML statement cannot have 
any enabled triggers if the statement contains 
an OUTPUT clause without INTO clause. 
(334) (SQLExecDirectW); [42000] 
[Microsoft][ODBC SQL Server Driver]
[SQL Server]Statement(s) could not be prepared. (8180)")

What should I do? I must use sqlalchemy. If one can give an advice to me, how can I implement versioning without triggers, it'd be cool.

Answer 1

You should set 'implicit_returning' to 'False' to avoid "OUTPUT" usage in query generated by SQLAlchemy (and this should resolve your issue):

class Company(sqla.Model):
    __bind_key__ = 'dbnamere'
    __tablename__ = 'tblnamehere'
    __table_args__ = {'implicit_returning': False}  # http://docs.sqlalchemy.org/en/latest/dialects/mssql.html#triggers
    id = sqla.Column('ncompany_id', sqla.Integer, primary_key=True)
    ...