|
|
Posted by Malc on 10/23/06 22:10
steven@bluemania.co.uk wrote:
> Does anyone know how to do the following. I'm trying to mimic
> replication with triggers.
>
> I have 2 databases, each have these 2 tables.
>
> 1.
> USERS
> ID int
> NAME varchar(20)
>
> 2.
> CHANGES
> TABLE varchar(20)
> TYPE varchar(10)
> col1 varchar(20)
> col2 varchar(20)
>
> On the USERS table I have a for insert trigger. Whenever a new user is
> added the trigger puts an entry into the CHANGES table such as
> ("USERS", "INSERT", "1", "Fred")
>
> I now have an application (vb.net) that monitors the CHANGES table on
> server1. If it finds an entry it determines the table using the TABLE
> column and performs the necessary insert and deletes the entry from
> CHANGES. Now the problem is server2 also has an for insert trigger on
> the USERS table so it puts an entry into CHANGES on server2. As you
> can imagine this goes around in a loop.
>
> What I was hoping for was someway of saying, "I'm inserting from my
> application so don't do the trigger".
>
> Any ideas gratefully appreciated.
>
> Steve.
Hi
I've had to do a similar thing and I achieve it as follows:
1. Ensure that the "replicating" application is running as a specific
user that nothing else.
2. Write each of your triggers in the following way:
create trigger mytrigger for insert
as begin
if system_user <> 'domain_name\special_user'
begin
-- Rest of trigger stuff goes here
end -- End of if statement
end -- End of trigger
Now the trigger will only actually do anything if a normal user does
it.
This stops stuff going backwards and forwards nicely.
Malc
Navigation:
[Reply to this message]
|