HostWeb Forums » Microsoft Databases » microsoft.public.sqlserver.replication » trigger problems and transactional replication
Topic: trigger problems and transactional replication
I have a major issue going on with transactional replication and triggers.
There are 2 tables: Product and Asset. When a product price is updated, then
it fires a trigger that updates the price * units AND a few other date
fields for every instance of that product in the asset table. So on the
production server it will be 1 update to the product table and then 1 update
that is fired on the Asset table.
The problem is of course, the with the replication. Almost every night the
product prices are updated. Almost 200K rows. This is the nature of these
products which change price (stocks and such) on a daily basis. The Asset
table can have anywhere from 1-1000+ rows for each product. This is killing
my latency on my replicated servers.
There will also situtions where the Asset table is frequently updated on
it's own that has nothing to do with the trigger.
How can I fix this drag? When updating the Asset table from the trigger, I
would prefer the system use a customized sp or something similar that does 1
mass update instead of each and every row. Below is an example of what I am
doing.
Update
Asset
Set
[Value] = Units * Product.ProductPrice,
Asset.MyDate = Product.ProductDate,
Asset.LastModified = getdate()
From
Asset
join dbo.Product
on Asset.ProductID = Product.ProductID
where Asset.ProductID = @ProductID
(Please note: I did not designed this nor am I allowed to restructure it at
this time. Thought I'd get that out of the way before all the comments came
that were heading that direction..)
Thanks SO MUCH in advance,
Kristy
Replies below ↓
Replies
Re: trigger problems and transactional replication
I would wrap the update in a stored procedure and replicate the execution of
that stored procedure.
So if you update the product table the stored procedure would update the
asset table instead of the trigger. Then on the subscriber this proc will
fire again and update both tables there as well.
Your latency should be near real time then.
"Kristy" <forums@houseofdedon.com> wrote in message
news:Oqb7gjoxIHA.4912@TK2MSFTNGP03.phx.gbl...
>I have a major issue going on with transactional replication and triggers.
>
> There are 2 tables: Product and Asset. When a product price is updated,
> then it fires a trigger that updates the price * units AND a few other
> date fields for every instance of that product in the asset table. So on
> the production server it will be 1 update to the product table and then 1
> update that is fired on the Asset table.
>
> The problem is of course, the with the replication. Almost every night the
> product prices are updated. Almost 200K rows. This is the nature of these
> products which change price (stocks and such) on a daily basis. The Asset
> table can have anywhere from 1-1000+ rows for each product. This is
> killing my latency on my replicated servers.
>
> There will also situtions where the Asset table is frequently updated on
> it's own that has nothing to do with the trigger.
>
> How can I fix this drag? When updating the Asset table from the trigger, I
> would prefer the system use a customized sp or something similar that does
> 1 mass update instead of each and every row. Below is an example of what I
> am doing.
>
>
> Update
> Asset
> Set
> [Value] = Units * Product.ProductPrice,
> Asset.MyDate = Product.ProductDate,
> Asset.LastModified = getdate()
> From
> Asset
> join dbo.Product
> on Asset.ProductID = Product.ProductID
> where Asset.ProductID = @ProductID
>
>
> (Please note: I did not designed this nor am I allowed to restructure it
> at this time. Thought I'd get that out of the way before all the comments
> came that were heading that direction..)
>
> Thanks SO MUCH in advance,
> Kristy
>
>
Re: trigger problems and transactional replication
I tried that and when I tested it through Profilier it still was updating on
each individual row for the Asset table.
Any thoughts on what I might be doing wrong? I still have to replicate both
tables right? Other wise other natural updates to these tables would not
occur on the subscriber. I put the stored proc to be executed in the
trigger, is that correct?
--Kristy
"Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
news:OQfr2YlyIHA.1772@TK2MSFTNGP03.phx.gbl...
>I would wrap the update in a stored procedure and replicate the execution
>of that stored procedure.
>
> So if you update the product table the stored procedure would update the
> asset table instead of the trigger. Then on the subscriber this proc will
> fire again and update both tables there as well.
>
> Your latency should be near real time then.
>
> "Kristy" <forums@houseofdedon.com> wrote in message
> news:Oqb7gjoxIHA.4912@TK2MSFTNGP03.phx.gbl...
>>I have a major issue going on with transactional replication and triggers.
>>
>> There are 2 tables: Product and Asset. When a product price is updated,
>> then it fires a trigger that updates the price * units AND a few other
>> date fields for every instance of that product in the asset table. So on
>> the production server it will be 1 update to the product table and then 1
>> update that is fired on the Asset table.
>>
>> The problem is of course, the with the replication. Almost every night
>> the product prices are updated. Almost 200K rows. This is the nature of
>> these products which change price (stocks and such) on a daily basis. The
>> Asset table can have anywhere from 1-1000+ rows for each product. This is
>> killing my latency on my replicated servers.
>>
>> There will also situtions where the Asset table is frequently updated on
>> it's own that has nothing to do with the trigger.
>>
>> How can I fix this drag? When updating the Asset table from the trigger,
>> I would prefer the system use a customized sp or something similar that
>> does 1 mass update instead of each and every row. Below is an example of
>> what I am doing.
>>
>>
>> Update
>> Asset
>> Set
>> [Value] = Units * Product.ProductPrice,
>> Asset.MyDate = Product.ProductDate,
>> Asset.LastModified = getdate()
>> From
>> Asset
>> join dbo.Product
>> on Asset.ProductID = Product.ProductID
>> where Asset.ProductID = @ProductID
>>
>>
>> (Please note: I did not designed this nor am I allowed to restructure it
>> at this time. Thought I'd get that out of the way before all the comments
>> came that were heading that direction..)
>>
>> Thanks SO MUCH in advance,
>> Kristy
>>
>>
>