Replication Triggers

Is there such a thing as a "Replication BLOB Trigger?"
Enover
I have never heard this term before, can you give us an example of what you
are talking about?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Enover" <Enover@.discussions.microsoft.com> wrote in message
news:877A44BD-FFCE-4DE7-AF74-4D5B82072E6A@.microsoft.com...
> Is there such a thing as a "Replication BLOB Trigger?"
> --
> Enover
|||We are in the process of updating our software and trying to find ways to
reduce the update time. The vendor sent us an email stating that we need to
evaluate replication blob triggers and decide whether they need adjustment or
additions.
My first question (red-flag), was do we mess with replication triggers? If
we adjust the triggers, who would future hotfixes or Service Packs impact the
adjustments we made.
However, since I have never heard of such a term and could not find it any
documentation on Microsoft site, I thought I would ask. Do replication
triggers (update, delete , insert) treat ntext, text or image fields
different from other columns?
Enover
"Hilary Cotter" wrote:

> I have never heard this term before, can you give us an example of what you
> are talking about?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Enover" <Enover@.discussions.microsoft.com> wrote in message
> news:877A44BD-FFCE-4DE7-AF74-4D5B82072E6A@.microsoft.com...
>
>
|||Press the vendor on what they are referring to. There is no such thing.
What they may be referring to is that certain replication types - queued,
immediate, and merge - do not handle text and image columns well.
For queued - From BOL - Queued Updating Considerations
The Subscriber cannot update or insert text or image values because they
cannot be read from the inserted or deleted tables inside the trigger.
Similarly, the Subscriber cannot update or insert text or image values using
WRITETEXT or UPDATETEXT because the data is overwritten by the Publisher.
Instead, you could partition the text and image columns into a separate
table and modify the two tables within a transaction. Use merge replication
to synchronize these values. You cannot be assured there are no conflicts
because the update of the text or image table can occur if the data is not
well partitioned.
For immediate - From BOL - Immediate Updating Considerations
The Subscriber cannot update or insert text or image values because they
cannot be read from the inserted or deleted tables inside the trigger.
Similarly, the Subscriber cannot update or insert text or image values using
WRITETEXT or UPDATETEXT because the data is overwritten by the Publisher.
Instead, you could partition the text and image columns into a separate
table and modify the two tables within a transaction. You could use merge
replication to synchronize these values if updates to text or image columns
are needed at the Subscriber. You can be assured there are no conflicts if
all updates follow this guideline because the update of the text or image
table cannot occur unless the main table was updated, which is protected by
2PC.
For merge - From BOL - Planning for Merge Replication
text and image Data Types in Merge Replication
Merge replication supports the replication of text, ntext, and image columns
only if they have been updated explicitly by an UPDATE statement because it
causes a trigger to fire that updates meta data ensuring that the
transaction gets propagated to other Subscribers.
Using only the WRITETEXT and UPDATETEXT operations will not propagate the
change to other sites. If your application uses WRITETEXT and UPDATETEXT to
update the text or ntext columns, explicitly add a dummy UPDATE statement
after the WRITETEXT or UPDATETEXT operations, within the same transaction,
to fire the trigger and thereby guarantee that the change will be propagated
to other sites.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Enover" <Enover@.discussions.microsoft.com> wrote in message
news:CE716760-EFE8-4737-9110-0C64CE23E7A6@.microsoft.com...[vbcol=seagreen]
> We are in the process of updating our software and trying to find ways to
> reduce the update time. The vendor sent us an email stating that we need
> to
> evaluate replication blob triggers and decide whether they need adjustment
> or
> additions.
> My first question (red-flag), was do we mess with replication triggers?
> If
> we adjust the triggers, who would future hotfixes or Service Packs impact
> the
> adjustments we made.
> However, since I have never heard of such a term and could not find it any
> documentation on Microsoft site, I thought I would ask. Do replication
> triggers (update, delete , insert) treat ntext, text or image fields
> different from other columns?
>
> --
> Enover
>
> "Hilary Cotter" wrote:
|||No, you do not want to "mess" with the triggers that replication creates.
That doesn't mean you can't do that, but you will not be supported if you
do.
Also, there is no such thing as replication blob triggers. Ask the vendor
give you an explicit example of what a replication blob trigger is including
sample code.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Enover" <Enover@.discussions.microsoft.com> wrote in message
news:CE716760-EFE8-4737-9110-0C64CE23E7A6@.microsoft.com...[vbcol=seagreen]
> We are in the process of updating our software and trying to find ways to
> reduce the update time. The vendor sent us an email stating that we need
> to
> evaluate replication blob triggers and decide whether they need adjustment
> or
> additions.
> My first question (red-flag), was do we mess with replication triggers?
> If
> we adjust the triggers, who would future hotfixes or Service Packs impact
> the
> adjustments we made.
> However, since I have never heard of such a term and could not find it any
> documentation on Microsoft site, I thought I would ask. Do replication
> triggers (update, delete , insert) treat ntext, text or image fields
> different from other columns?
>
> --
> Enover
>
> "Hilary Cotter" wrote:
|||Thank you all for your help.
Enover
"Michael Hotek" wrote:

> No, you do not want to "mess" with the triggers that replication creates.
> That doesn't mean you can't do that, but you will not be supported if you
> do.
> Also, there is no such thing as replication blob triggers. Ask the vendor
> give you an explicit example of what a replication blob trigger is including
> sample code.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Enover" <Enover@.discussions.microsoft.com> wrote in message
> news:CE716760-EFE8-4737-9110-0C64CE23E7A6@.microsoft.com...
>
>