SQL Server触发器和sqlalchemy干扰问题。需要帮助

Question

我需要对一些关键表进行某种“版本控制”，并尝试以一种相当简单的方式实现它:

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,
...
)

以及每个表的插入/更新/删除上的一堆触发器，旨在存储其更改。

我的应用程序基于 PyQt + sqlalchemy，当我尝试插入存储在版本化表中的实体时，sqlalchemy 会引发错误:

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)")

我该怎么办？我必须使用 sqlalchemy。如果有人能给我建议，如何在没有触发器的情况下实现版本控制，那就太酷了。

Answer 1

您应该将“implicit_returning”设置为“False”以避免在 SQLAlchemy 生成的查询中使用“OUTPUT”(这应该可以解决您的问题):

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)
    ...