如何防止特定的INSERT语句在事务中回滚

Question

我有一个非常庞大的存储过程(SP)，其中包含一个事务。我在SP中运行一个算法，如果该算法失败，则会回滚事务。

即使事务被回滚，我仍然需要记录一些数据，但是当事务被回滚时，日志也会被回滚。这是正常的行为，但是我需要将这些日志插入语句从回滚中排除，以便仍然记录事务。

我有一个临时表称为#MissingAllocationLines，我将我的日志插入到该表中。然后，如果它回滚，我需要将#MissingAllocationLines中的所有行插入到名为DLWMS_ALLOCATIONMISSINGLOG的真实表中。

这可能吗？以下是我的示例代码：

create table #MissingAllocationLines
(ALLOCATIONJOBID BIGINT,
ORDERID BIGINT,
ORDERDETAILID BIGINT,
ITEMID BIGINT,
STOCKQUANTITY BIGINT,   
ORDERQUANTITY BIGINT)


BEGIN TRANSACTION

WHILE(.....)
BEGIN
    INSERT INTO #MissingAllocationLines (ALLOCATIONJOBID,ORDERID,ORDERDETAILID,ITEMID,STOCKQUANTITY,ORDERQUANTITY)
    VALUES (@ALLOCATIONJOBID,@OrderID,@OrderDetailID,@ItemID,ISNULL(@StockFreeQuantity, 0),ISNULL(@RemainingQuantity,0))
    ...
    ...
    ...
END

IF(@DONE=1)
BEGIN
    COMMIT TRANSACTION
END
ELSE
BEGIN
    ROLLBACK TRANSCATION

    INSERT INTO DLWMS_ALLOCATIONMISSINGLOG (ALLOCATIONJOBID,ORDERID,ORDERDETAILID,ITEMID,STOCKQUANTITY,ORDERQUANTITY)
    SELECT ALLOCATIONJOBID,ORDERID,ORDERDETAILID,ITEMID,STOCKQUANTITY,ORDERQUANTITY
    FROM #MissingAllocationLines
END 

- Arif YILMAZ

---

Oracle 中有一种称为“自主事务”的东西，可以用于此目的。不知道 SQL Server 是否有类似的功能。 - Mat

Answer 1

尝试使用表变量而不是临时表。表变量不参与事务。

http://zarez.net/?p=1977

http://www.sqlservercentral.com/blogs/steve_jones/2010/09/21/table-variables-and-transactions/

- Joe C

Answer 2

在 catch 块中，在回滚事务之前，执行以下操作...

 DECLARE @TABLE AS TABLE
    (COL1 INT,
     COL2 INT
    ...
     )
     INSERT INTO @TABLE 
     SELECT * FROM #TEMP TABLE

    ROLLBACK TRANSCATION

    INSERT INTO DLWMS_ALLOCATIONMISSINGLOG 
     select * from @table

References:
https://www.itprotoday.com/sql-server/table-variable-tip

- TheGameiswar

Answer 3

您可以将“remote proc transaction promotion”设置为false，并使用该链接服务器将日志记录到本地服务器：以下是代码

begin tran outertran
insert into t values (1)
begin tran innertran
insert into localserver.tempdb.#log values (1)
commit tran innertran
IF (@Done) 
begin 
     commit tran outertran
end
else begin
rollback tran outertran
end 

select * from #log

EXEC sp_addlinkedserver @server = N'localserver',@srvproduct = N'',@provider = N'SQLNCLI', @datasrc = @@SERVERNAME
EXEC sp_serveroption localserver,N'remote proc transaction promotion','FALSE'
EXEC sp_serveroption localserver,N'RPC OUT','TRUE' -- Enable RPC to the given server

- Kannan Kandasamy

Answer 4

请查看SAVE TRANSACTION命令。

- MysticHeroes