User's Manual

Triggers at the
consolidated database
only
The values in the subscription-list column are maintained by triggers. These
triggers fire at the consolidated database when the triggering inserts or
updates are applied by the Message Agent. The triggers must be excluded
from the remote databases, as they maintain a column that does not exist.
You can use the sp_user_extraction_hook procedure to exclude only
certain triggers from a remote database on extraction. The procedure is
called as the final part of an extraction. By default, it is empty.
❖ To customize the extraction procedure to omit certain triggers
1. Ensure the quoted_identifier option is set to ON:
set quoted_identifier on
go
2. Any temporary tables referenced in the procedure must exist, or the
CREATE PROCEDURE statement will fail. The temporary tables
referenced in the following procedure are available in the ssremote.sql
script. Copy any required table definitions from the script and execute the
CREATE TABLE statements, so they exist on the current connection,
before creating the procedure.
3. Create the following procedure:
CREATE PROCEDURE sp_user_extraction_hook
AS
BEGIN
-- We do not want to extract the INSERT and
-- DELETE triggers created on the Policy table
-- that maintain the subscription_list
-- column, since we do not include that
-- column in the publication.
-- If these objects were extracted the
-- INSERTs would fail on the remote database
-- since they reference a column
-- ( subscription_list ) that does not exist.
DELETE FROM #systrigger
WHERE table_id = object_id( ’Policy’ )
-- Do not create any procedures
DELETE FROM #sysprocedure
WHERE proc_name = ’SubscribeCustomer’
END
go
Tuning extraction performance for shared rows
When extracting or synchronizing a user, the subscription-list column can
cause performance problems as it necessitates a full table scan.
162