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.