Triggersat the
consolidateddatabase
only
Thevalues in the subscription-list column are maintained by triggers. These
triggersfire at the consolidated database when the triggering inserts or
updatesare applied by the Message Agent. The triggers must be excluded
fromthe remote databases, as they maintain a column that does not exist.
Youcan use the sp_user_extraction_hook procedure to exclude only
certaintriggers from a remote database on extraction. The procedure is
calledas the final part of an extraction. By default,it is empty.
❖Tocustomize the extraction procedure to omit certain triggers
1. Ensurethe quoted_identifier option is set to ON:
set quoted_identifier on
go
2. Any temporary tables referenced in the procedure must exist, or the
CREATEPROCEDURE statement will fail. The temporary tables
referencedin the following procedure are available in the
ssremote.sql
script. Copy any required table definitions from the script and execute the
CREATETABLE statements, so they exist on the current connection,
beforecreating the procedure.
3. Createthe 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
Tuningextraction performance for shared rows
Whenextracting or synchronizing a user, the
subscription-list
columncan
causeperformance problems as it necessitates a full table scan.
162