0

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.

  • Is the trigger created as NOT FOR REPLICATION ? – Kin Shah Mar 16 '15 at 20:28
  • where do i find that? – jmzagorski Mar 16 '15 at 20:50
  • Assuming your publisher and the subscriber are two different sql instances, then I am confused regarding your statement "we are not getting the new value back from the subscriber's trigger on the publisher after the insert occurs." because how can you get the value of a subscriber from the publisher? Can you elaborate please? – jyao Feb 6 '18 at 21:38
  • The insert to the subscriber table is a completely independent transaction on a completely different server to the insert on the publisher. There's no way the publisher can see the value from the trigger, as it is not fired during the insert on the publisher. – Gareth Lyons Feb 9 '18 at 19:11
  • ha, that makes perfect sense now (3 years later saying to myself: duh!). I was brand new to replication at the time I guess I thought it would just work and didn't take the time to think about it (and never had to revisit this issue or revise my workaround). If you want to put that as the answer I will mark it. – jmzagorski Feb 9 '18 at 23:54
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.