将插入表和删除表保存到变量中以便在 .NET 代码中使用（通过 sp_oamethod）[SQL Server 2000]

Question

我正在尝试在 SQL Server 中创建一个 SQL 触发器，它将以某种方式序列化插入和删除表以在 .NET 代码中使用（通过 sp_oamethod）。我希望它足够通用，可以用于任何表。

我的第一次尝试涉及使用“for xml”将其序列化为 XML，并将其传递给 .NET 代码。但是，我无法将 XML 分配给变量，因为 SQL Server 2000 不支持它（2005 还不是我们的选择）。唯一的选择是手动进行序列化（请参阅保存FOR XML AUTO 结果到 SQL 中的变量），这不符合我的通用要求。

--This does not work
declare @OldValue varchar(5000)
select @OldValue = (select * from Deleted for XML auto)

有谁知道使用任何方法一般可以做到这一点的方法吗？我不关心格式，只要我可以将列名称和值放入我的 .NET 代码中即可。

Answer 1

您确实了解触发器发生在事务内，并且您尝试的触发器会延长事务，更重要的是，依赖于 SQL Server 外部的进程可用并无错误地完成，对吗？

我建议不要在触发器中执行此操作，特别是 sp_OA* 内容。您可以通过将插入和删除表的结果排队到临时表中并让作业或 Windows 服务处理数据来完成您需要的任务吗？

Answer 2

您是否可以在没有变量的情况下完成此操作，例如：

select * from Deleted for XML auto

使触发器返回 XML 结果集？

或者，在触发器内：

declare @tablename varchar(256) 
set @tablename = quotename(object_name(@@procid)+'_'+convert(varchar,@@spid))
select * into #inserted from inserted
exec ('select * into '+@tablename+' from #inserted')
exec dbo.myGenericXML_generator @tablename
exec ('drop table '+@tablename)

有点巴洛克风格，但如果你的音量足够小，它可能会起作用。您还需要添加错误处理，这只是传达了这个想法。

或者，在将数据复制到永久表后，您可以将一个条目添加到队列中，并分别有一些进程来消耗队列并删除表。

更好的是，编写一些代码来自动生成触发器以及系统表之外的所有 XML 生成代码。即，代码生成代码生成XML。