June 28, 2005 at 12:14 pm
Hi, we are trying to use replication with trigger insert data only. Is there a way to do this with SQL 2000. I don't believe that SQL 2005 is a solution at this time for us. If not through replication, how about DTS?
Thanks for any help on this subject.
June 28, 2005 at 3:10 pm
If what you mean is that the trigger inserts data in a destination table and that table is replicated then yes it is possible to do that but if what you mean is that the trigger inserts data on the same table that fire it then the answer is no because a table is either published or it isn't , no middle ground
hth
* Noel
June 28, 2005 at 5:15 pm
The trigger is on the publisher not on the subscriber. If the backup can do differencial, then is there a column within the table or a table that denotes what the trigger is (ie. insert, delete, update)? I wish to use the trigger from the publisher to send only insert data to the subscriber.
June 29, 2005 at 8:17 am
Instead of using replication (for this table), you could put code in the trigger to copy any new rows to a table on the subscriber.
In a FOR INSERT, UPDATE trigger:
INSERT INTO <subscriber table>
SELECT * FROM inserted
WHERE pk NOT IN (SELECT pk FROM deleted)
This is probably not a good idea unless you have a reliable low-latency connection between the servers, because every action on the published table is now a distributed transaction. And whoever inheirits your job if you move on is going to have a hell of a time figuring out how that table is being replicated.
Or, you could use the code above to copy new rows to a spooling table in the same database, replicate the spooling table, then use a trigger on the subscriber spooling table to copy any inserts to a permanent table on the subscriber. Add a scheduled job to periodically delete all rows from the publisher spooling table and it might do what you want. This is kind of complicated, but it wouldn't have the performance issues of the distributed transaction.
Finally, you could dig into the stored procedures generated to do the replication for that table and customize them so they don't do updates. Deletes are another issue, you don't say whether rows in the published table are ever deleted. You'd get errors if rows are not deleted on the subscriber, but you deleted & reinserted the same pk on the publisher.
July 7, 2005 at 10:39 am
This is a hack and wouldn't be supported by MS, but you could create a merge publication and then remove the update and delete triggers that replication creates on each published table. This will leave behind the insert trigger and only inserts will propogate to the subscribers.
There are many down sides to this approach, but if you have a valid reason to send inserts only this may work for you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply