Saving Inserted and Deleted tables into variables for use in .NET code (via sp_oamethod) [SQL Server 2000]

Question

I am trying to create a SQL Trigger in SQL Server that will somehow serialize the Inserted and Deleted tables for use in .NET code (via sp_oamethod). I would like this to be generic enough to use for any table.

My first attempt involved using "for xml" to serialize it into XML, and pass it to .NET code. However, I have been unable to assign the XML to a variable, as it is not supported in SQL Server 2000 (2005 is not yet an option for us). The only option is to do the serialization manually (Ref. http://stackoverflow.com/questions/914009/saving-the-for-xml-auto-results-to-variable-in-sql) and that does not fit my requirements of being generic.

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

Does anyone know of a way to do this generically, using any method? I do not care about the format, as long as I can get the column names and values into my .NET code.

Answer 1

Can you do it without a variable, eg:

select * from Deleted for XML auto

Such that the trigger returns an XML result set?

Alternatively, within the trigger:

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)

Kinda baroque, but if you volume is small enough, it might work. You would also want to add error handling, this just communicates the idea.

Or, after you copied the data to a permanent table, you could add an entry to a queue, and separately have some process that consumed the queue and dropped the tables.

Better yet, write some code to auto-generate your triggers and all the XML generating code off the system tables. ie, code generating code generating XML.

Answer 2

You do understand that triggers take place within a transaction and that one you are attempting will lengthen the transaction and, even more importantly, rely on processes outside of SQL Server being available and completing without error, right?

I'd recommend against doing this in a trigger, especiall the sp_OA* stuff. Can you accomplish what you need by queuing the results from the inserted and deleted tables in a staging table and having a job or windows service process the data?