Hi All,

I have a problem with using Triggers. I have 2 tables which have the same columns but are situated on 2 different databases. I need to write triggers for Insert, Update on both databases, so that if I Insert a record in the Table in Database 1 it should be reflected in the Table in Database 2.Same vice-versa. There are Unique Keys on both sides on which Insert,update Triggers would run.

Kindy help me in how to go about it.

I am having problems in while Inserting as Inserting in Database Table is firig the Triggers for the table in the Other Database and resulting in error.

I can think of three options that may help, it depends somewhat on what is meant by different databases, are they on the same server or different ones?
 

• Look at setting nested triggers to 0 at some stage
• Use replication instead of triggers
• Examine the CONTEXT_INFO before executing trigger code, you can set the CONTEXT_INFO yourself to disable the second trigger

--

Joe (Microsoft MVP - XML)

As another really hacky way to do this (not recommended, but if for some reason Joe's suggestions can't work in your situation), you *could* simply add a dummy field to each table that your normal INSERT and UPDATE operations ignore (e.g., use a DEFAULT of NULL or some such). But in the trigger code, you purposely use a non-default value when you replicate the records into the other DB. And then the trigger code looks for a non-default value and does nothing when such is found.

Ugh. I can't believe I'm suggesting this. Please try Joe's suggestions first! (Especially see if you can't just use replication.)

Hi Joe & Old Pedant,

Thanks for your suggestions. Since the databases are on the same server I would first try setting nested triggers with Update and try setting them to 0. I am not too confident on replication so I would rather try this first.


Thanks again,
Arny