November 17, 2016 at 8:12 am
We have transactional replication set up. On the subscriber, there's a table with 2 triggers on it. Both are UPDATE triggers with different conditional logic in each. Both triggers update the same table when they fire. Occasionally a record doesn't make it into the destination table. It gets to the table with the triggers on it, but not to the table the trigger writes to.
The conditions in the tables take the form of
IF UPDATE (ColumnName)
and
IF UPDATE (ColumnName) OR UPDATE(OtherColumnName)
Any ideas?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 17, 2016 at 9:29 am
Need quite a bit more detail here to help.
Anytime code works sometime and not others you need to take a REALLY close look at the logic and especially conditionals/predicates involved.
Also, are you speaking about failure to get the record into the replication construct or just the primary table?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 17, 2016 at 9:35 am
Do the triggers (or either of them) have the NOT FOR REPLICATION property?
John
November 17, 2016 at 9:45 am
John Mitchell-245523 (11/17/2016)
Do the triggers (or either of them) have the NOT FOR REPLICATION property?John
Nope
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 17, 2016 at 9:49 am
TheSQLGuru (11/17/2016)
Need quite a bit more detail here to help.Anytime code works sometime and not others you need to take a REALLY close look at the logic and especially conditionals/predicates involved.
Also, are you speaking about failure to get the record into the replication construct or just the primary table?
The Failure is after the trigger on the subscriber.
Update record in Source -> [Replication] -> Subscriber table -> [2 update triggers] -> Destination table
The record is getting into the Subscriber table, but not the destination table
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 17, 2016 at 9:52 am
Sioban Krzywicki (11/17/2016)
TheSQLGuru (11/17/2016)
Need quite a bit more detail here to help.Anytime code works sometime and not others you need to take a REALLY close look at the logic and especially conditionals/predicates involved.
Also, are you speaking about failure to get the record into the replication construct or just the primary table?
The Failure is after the trigger on the subscriber.
Update record in Source -> [Replication] -> Subscriber table -> [2 update triggers] -> Destination table
The record is getting into the Subscriber table, but not the destination table
Does the trigger fire at all when the replicated updates hit the sub table?
Do SOME of the records from the triggers make it to dest table or NONE of them?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 17, 2016 at 11:05 am
TheSQLGuru (11/17/2016)
Sioban Krzywicki (11/17/2016)
TheSQLGuru (11/17/2016)
Need quite a bit more detail here to help.Anytime code works sometime and not others you need to take a REALLY close look at the logic and especially conditionals/predicates involved.
Also, are you speaking about failure to get the record into the replication construct or just the primary table?
The Failure is after the trigger on the subscriber.
Update record in Source -> [Replication] -> Subscriber table -> [2 update triggers] -> Destination table
The record is getting into the Subscriber table, but not the destination table
Does the trigger fire at all when the replicated updates hit the sub table?
Do SOME of the records from the triggers make it to dest table or NONE of them?
Nearly all make it to the dest table. 1 in a thousand doesn't make it.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 17, 2016 at 7:31 pm
Assuming you can guarantee that that 1-1000 update actually made it into the table with the trigger on it then the most likely scenario I can think of by far (without additional information anyway) is a flaw in the trigger logic.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply