Sql-server – SQL Server Transactional Replication With subscriber trigger returning a value

sql servertransactional-replicationtrigger

I have transaction replication setup on on SQL server 2008 R2. The subscriber has a after trigger on the published article that inserts into another table on the subscriber's database and returns the new value created in the trigger (the column is a persisted computed column). However, it seems we are not getting the new value back from the subscriber's trigger on the publisher after the insert occurs.

Is SQL server able to return the value from the trigger or because replication is transnational, which is just one way, it cannot?

UPDATE (2018-02-09 many many years later)

So hopefully I can better explain this than my past self:

A publisher, on a different SQL Server than the subscriber, publishes a table called dbo.Inventory. The subscriber subscribes to this publication and now has dbo.Inventory on its server. We add an AFTER INSERT trigger on the published table, dbo.Inventory, on the subscriber's SQL Server:

CREATE TRIGGER [dbo].[aiInsertNewPk] 
  ON  [dbo].[Inventory] -- remember this trigger is on the subscriber
  AFTER INSERT
AS 
BEGIN

SET NOCOUNT ON;

DECLARE @Serial TABLE (ColumnA CHAR(10));

INSERT INTO [Subscriber].[DifferentTable] ( <...columns> )
OUTPUT inserted.[Foobar] INTO @Serial(ColumnA)
SELECT <...columns>
FROM inserted
WHERE <logic>

-- output this value so c# application can grab it
SELECT ColumnA FROM @Serial

END

Our problem some years ago, was that trigger was not outputting the values from ColumnA (the last line of the trigger), so our C# application was always getting a null value back.

Not sure if that helps and honestly I have not revisited this issue to see if the problem was something else entirely. I just thought I would add this update since you were kind enough to comment many years later.

Best Answer

this isn't so much an answer as a workaround. I am saving the value generated in the trigger on a table on the subscriber with the primary key of the transaction from the publisher. Then the application is querying that table on the subscriber immediately after the insert.