SQL Server Updating temporal table history

z9gpfhce 于 2023-08-02 发布在其他

关注(0)|答案(3)|浏览(109)

I'm having a problem updating rows in a temporal history table in MS SQL Server 2016.

From the documentation, the steps that should be needed are as follows:

Disable system versioning
Modify the history table
Enable system versioning again

I tried creating a procedure that does this, but got this error:
Msg 13561, Level 16, State 1, Line 23 Cannot update rows in a temporal history table 'db.dbo.FooHistory'.

Here is my SQL:

CREATE TABLE Foo(
    id int primary key not null
    , title nvarchar(50) not null
    , startTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
    , endTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
    , PERIOD FOR SYSTEM_TIME (startTime, endTime) )

ALTER TABLE Foo
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.FooHistory));

GO

CREATE PROCEDURE [dbo].[UpdateFooHistory] AS
  BEGIN
    SET NOCOUNT ON

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION;
    ALTER TABLE dbo.Foo SET (SYSTEM_VERSIONING = OFF);      

    UPDATE dbo.FooHistory
    SET title = 'Foo';

    ALTER TABLE dbo.Foo SET (SYSTEM_VERSIONING = ON (
      HISTORY_TABLE = dbo.FooHistory,
      DATA_CONSISTENCY_CHECK = ON
    ));

    COMMIT TRANSACTION

    RETURN 0
  END
GO

It seems like SQL Server is checking if a table is temporal at "compile time" rather than at runtime. Is this true? Is there a way to work around it?

sql-server

来源：https://stackoverflow.com/questions/46737825/updating-temporal-table-history

3条答案

按热度按时间

iswrvxsc1#

Change the UPDATE statement to this, and it will let you create the procedure:

EXEC(N'UPDATE dbo.FooHistory SET title = ''Foo''');

赞(0）回复(0）举报 2023-08-02

a64a0gku2#

With SQL Server 2019 it does not work any more (neither with exec nor with sp_executesql). You need to use a workaround.

In order to prevent any other user to change something while the versioning is off first start a serializable transaction:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
BEGIN TRANSACTION;

Temporarily disable SYSTEM_VERSIONING on the table:

ALTER TABLE Foo SET (SYSTEM_VERSIONING = OFF);

Modify the history table:

UPDATE dbo.FooHistory SET title = 'Foo';

Re-enable the versioning:

ALTER TABLE Foo SET (SYSTEM_VERSIONING = ON (  HISTORY_TABLE = dbo.FooHistory, DATA_CONSISTENCY_CHECK = ON));

Commit the transaction:

COMMIT TRANSACTION;

赞(0）回复(0）举报 2023-08-02

enxuqcxy3#

With SQL Server 2022 (and Azure SQL), I see the error is being raised by the query parser before it even attempts to run the SET ( SYSTEM_VERSIONING = OFF ); part.

...and as with other errors raised by the parser before it even runs any DDL, the trick is to add a GObatching instruction so that the parser stops reading ahead and will run with what it read up-to-that point and only run the rest of the stateements when the previous statements complete.

I also had my statements wrapped in a transaction too (though using ISOLATION LEVEL SERIALIZABLE wasn't necessary):

Like so:

SET XACT_ABORT ON;

BEGIN TRANSACTION ddlTxn1;

ALTER TABLE dbo.MyTemporalTable SET ( SYSTEM_VERSIONING = OFF );

/* The `GO` separator below stops SQL Server from complaining about the UPDATE below before it runs the `SET ( SYSTEM_VERSIONING = OFF )` above: */

GO

UPDATE dbo.MyTemporalTable         SET Foo = 'bar';
UPDATE dbo.MyTemporalTable_History SET Foo = 'bar';

ALTER TABLE dbo.MyTemporalTable SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE          = dbo.MyTemporalTable_History,
        DATA_CONSISTENCY_CHECK = ON
    )
);

COMMIT TRANSACTION ddlTxn1;

SQL Server Updating temporal table history

3条答案

相关问题

热门标签

最新问答