Output clause and replication
Hello everyone,
Some of the developers here have written Insert/Update/Delete stored procedures that use the Output clause to return the affected row(s) to the application. Not thinking about this, I tried to start replicating (transactional with updatable subscribers) some of the tables that these stored procedures reference. Well, the replication setup process created triggers on these tables and then the stored procedures started to blow up because you can't have an Output clause without an Into on a table that has triggers enabled.
It looks like this now...
insert into Table1 (col1, col2, col3)
output inserted.*
select @value1, @value2, @value3
I've thought of an option that I could implement to get around this...
Create a temp table with the same structure as the table being updated (using select top 0 * ...), output into this temp table, then select everything from the temp table so that the client gets the records that were modified.
select top 0 * into #i from Table1 with (nolock)]
insert into Table1 (col1, col2, col3)
output inserted.* into #i
select @value1, @value2, @value3
select * from #i
Has anyone else came across this issue when setting up replication? If so, how did you get around it?
Can anyone think of any other options? Are there any downsides to doing it the way I mentioned?
Thanks in advance,
Jarret
모든 응답
Well, what I mentioned before won't work for me. By using the "select top 0 *" method, the identity property is copied to the temp table as well, so the Output Into clause fails. Is there a simple way to get rid of the identity property from the temp table?
Another option I thought of is to declare a table variable to hold just the ID's that were updated, output into it, then use a select query and join it to the table being updated. Would this be the best way to go?
Has anyone else came across this issue when setting up replication? If so, how did you get around it?
Jarret
