SQL 2000 is not replicating my triggers.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

SQL 2000 is not replicating my triggers.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-09 : 09:29:53

Alex writes "MS SQL 2000 is failing to replicate my triggers. I have a test table, zzzTEST, that I am replicating. zzzTEST looks like this...

CREATE TABLE [dbo].[zzzTEST] (
[test_pk] [int] IDENTITY (1, 1) NOT NULL ,
[field1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[field2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[field3] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[msrepl_tran_version] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[zzzTEST] WITH NOCHECK ADD
CONSTRAINT [DF__zzzTEST__msrepl___392B6C00] DEFAULT (newid()) FOR [msrepl_tran_version],
CONSTRAINT [PK_zzzTEST] PRIMARY KEY CLUSTERED
(
[test_pk]
) WITH FILLFACTOR = 95 ON [PRIMARY]
GO

I then have two triggers that I have created for this table...

CREATE trigger zzzTEST_Update on dbo.zzzTEST

for update as
declare @test_pk int,
@currentuser int,
@newstatus char(3),
@oldstatus char(3),
@pk_listenlog int,
@date datetime

SELECT @test_pk = test_pk
FROM inserted
WHERE test_pk in (select test_pk from inserted)

if update ( field1 )
update zzzTEST
set field2 = ('UPDATING'), field3 = 'PROCS'
where test_pk in (select test_pk from inserted)

CREATE TRIGGER zzzTEST_Insert on [dbo].[zzzTEST]
FOR INSERT AS
declare @test_pk int,
@currentuser int,
@newstatus char(3),
@oldstatus char(3),
@pk_listenlog int,
@date datetime

SELECT @test_pk = test_pk
FROM inserted
WHERE test_pk in (select test_pk from inserted)

if update ( field1 )
update zzzTEST
set field2 = ('INSERTING'), field3 = 'PROCS'
where test_pk in (select test_pk from inserted)

When I replicate my two databases, I am only replicating one table, zzzTEST, as a transactional replication, with immediate updating. Before I start the replication, I create the zzzTEST table and its triggers on the subscriber database. When I create the publication, I select zzzTEST as the only table to be replicated, and I select from the Table Article Properties, the Snapshot tab, where I select "Use Trigger" from Copy objects to destination.

When my replication is finished, I create a pull-subscription to it, and run the snapshot agent. The agent creates a snapshot of my publisher's table, zzzTEST, and all its data, into the subscriber database. However, since I created the triggers zzzTEST_Update and zzzTEST_Insert on my subscriber database before replication, I should expect them to still remain. zzzTEST_Update remains, but zzzTEST_Insert is over-written. Now I have 4 triggers where before I had 2, and they are...

trg_MSsync_del_zzzTEST
trg_MSsync_ins_zzzTEST
trg_MSsync_upd_zzzTEST
zzzTEST_Update

I cannot get my replication to work properly, b/c I cannot insert data into the subscription table, zzzTEST. I continuously get the following errors. "Another user has modified the contents of this table or view; the database row you are modifying no longer exists in the database. Database error:'[Microsoft][ODBC SQL Server Driver][SQL Server]Updatable Subscriptions: Rows do not match between Publisher and Subscriber. Run the Distribution Agent to refresh rows at the Subscriber. [Microsoft][ODBC SQL Server Driver][SQL Server]Updatable Subscriptions: Rolling back transaction."

If anyone can please offer some advice, you just may help me save my job and help put food on my table. Thanks for your time.

Alex"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-04-09 : 10:01:34

Generally speaking, you don't want triggers to come over in replication. Whatever data modifications result from triggers on the published articles are sent over in exactly the same fashion as changes occurring directly on the published articles themselves. You don't need the triggers on the subscriber to make that work ( particularly if you're using transactional [one-way] replication ).

If you have some kind of special need, then post additional comments and I'll do my best ...

.

setBasedIsTheTruepath
<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources