SQL Server触发如何记录所有操作

Question

我有一些触发器用于将数据复制到具有不同结构的另一个DB。

我需要这样做:

BEGIN TRY
if exists
UPDATE db2 set fld = INSERTED.fld
if not exists
INSERT INTO db2 ...
INSERT INTO log (text) values ('row inserted blabla'+:inserted.ID)
END TRY
TRY CATCH
INSERT INTO log (text) values ('insert failed blabla'+:ERROR_MESSAGE())
END CATCH;

首先，我使用游标，因为对于每一行，我需要插入或更新第二个数据库，我使用TRY..CATCH来处理错误。这是工作，但每个人都说我不应该使用游标的性能问题。

第二，我使用动态SQL将我所有的查询写入VARCHAR(MAX)，我在传递每条记录后执行，它需要100次。

类似:

DECLARE @SQL VARCHAR(MAX) = '';
SELECT @SQL = @SQL+ 'BEGIN TRY etc.....'+'END TRY'+'BEGIN CATCH INSERT INTO  INSERT INTO log (text) values ('''insert failed blabla'''+:ERROR_MESSAGE()+')END CATCH;' 
FROM INSERTED
EXECUTE (@SQL)

起初我在考虑使用MERGE语句，但我需要跟踪每条记录更新或插入，所以OUTPUT指令是不够的，因为我需要记录@@IDENTITY

的值等东西解决这个问题的最好方法是什么?

Answer 1

尝试使用OUTPUT子句。根据文档，即使有回滚，OUTPUT也能工作，因此您可能需要更多的错误检查。下面是您的伪代码中的一个快速示例:

BEGIN TRY

if exists
BEGIN
UPDATE db2 set fld = INSERTED.fld
OUTPUT INSERTED.ID, INSERTED.Name, INSERTED.ModifiedDate  
INTO @UpdateResults;
INSERT INTO log(text)
SELECT 'row updtaed blabla' + CAST(ID as nvarchar)
FROM @UpdateResults;
END
if not exists
BEGIN
INSERT INTO db2 ...
OUTPUT INSERTED.ID, INSERTED.Name, INSERTED.ModifiedDate  
INTO @InsertResults;
INSERT INTO log(text)
SELECT 'row inserted blabla' + CAST(ID as nvarchar)
FROM @InsertResults;
END

END TRY
TRY CATCH
INSERT INTO log (text) values ('insert failed blabla'+:ERROR_MESSAGE())
END CATCH;