need to write trigger which will update 3-4 table

Question

Post reply

need to write trigger which will update 3-4 table

hover

Right there with Babe

Points: 720
More actions
July 5, 2005 at 9:09 am

#186453

I need to write trigger which will update 3-4 table ,with relevant records.The sceneriao is as under.We have 2 databases server.We have set up the replication successfully with these 2 servers for database A .Now on the slave server we have a another db B which consist of 5 tables.We need to fire a trigger to insert,update,delete records into these 5 tables every time data is replicated on database A
on slave.The problem is that not the entire record is to be inserted from the replicated table.Only few coloumns is to be iserted,say col1,col5 , col9 to be inserted in table1;col1,col3,col8 to be insrted in table2 ;col 3,col6,col7 in table3 and so on.I am wondering how to achive this.Please help me in this .Its really very urgent.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

I guess the 1st question is:

WHY do you need to break the data into 5 different tables housing pieces\parts of the 1 table?

Good Hunting!

AJ Ahrens

webmaster@kritter.net

noeld SSC Guru Points: 96590 More actions · Answer 2

what is the problem with setting those triggers on the replicated DB ?

* Noel

hover Right there with Babe Points: 720 More actions · Answer 3

Its is due to requirement for a new application.its a matter of urgency

hover Right there with Babe Points: 720 More actions · Answer 4

The table on which I want to create the trigger is on db1 and the tables on which the trigger insert the result is on db2.Will triggers work with different databases.

Tomde-222717 Newbie Points: 5 More actions · Answer 5

There are a couple of ways to accomplish this. First you can have multiple triggers for one event. Or you could write a seperate stored procedure that would handle the updates and execute it from the triggers. Triggers do allow you to update tables on remote servers.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

Approach 1: Write the trigger on the subscriber table. Inserts on the published table will be replicated, then the trigger will fire there to do the additional inserts.

Approach 2: Lookup "Using Transformable Subscriptions to Create Custom Data Partitions" in BOL.

Approach 3: From BOL "Enhancing Transactional Replication Performance":

Use Custom Stored Procedures for Inserts, Updates, and Deletes at Subscribers

When Microsoft® SQL Server™ 2000 applies transactions at a Subscriber, by default it overrides the INSERT, UPDATE, and DELETE statements from the transaction log with custom stored procedures. For example, instead of applying the INSERT statement read from the transaction log, the Distribution Agent can run a stored procedure at the Subscriber to perform the same action. These stored procedures can be further customized, which is generally better than adding Subscriber-specific logic in triggers (for actions such as maintaining aggregate tables).