How do you conditionally fire a trigger (mimic replication)

[steven]

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.

[Alex Kuznetsov]

Steve,

If you absolutely do not want to use replication, and do not want to
add a column AddedOnServerNumber, make sure identities on one server
are odd (1,3,5,7,..) and on another are even(2,4,6,..)
Have you triggers act differently depending on inserted.ID % 2, or
based on AddedOnServerNumber.

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

[samh]

Alex,
Thanks for suggestion. Replication is not possible, the client this is
being developed for is insisting on not using it so I don't have much
choice.

The problem I have is that the example I gave is a basic version. I
have 1 central server and 6 local servers so giving odd/even isn't
possible. All the local servers are replicating their changes to the
central server and it's in turn replicating back down to the local
server which sent the data. The problem that I described above is
still essentially the problem I need to solve.

I thought there would of been a very easy way to turn of triggers but
this seems not the case?

Cheers,
Steve.

[Kart]

Hi,

You can't switch off triggers

Cant you have one more column to the changes table to tell which server
has inserted that data?

Kart

samh wrote:

Alex,
Thanks for suggestion. Replication is not possible, the client this is
being developed for is insisting on not using it so I don't have much
choice.

The problem I have is that the example I gave is a basic version. I
have 1 central server and 6 local servers so giving odd/even isn't
possible. All the local servers are replicating their changes to the
central server and it's in turn replicating back down to the local
server which sent the data. The problem that I described above is
still essentially the problem I need to solve.

I thought there would of been a very easy way to turn of triggers but
this seems not the case?

Cheers,
Steve.

[Damien]

samh wrote:

Alex,
Thanks for suggestion. Replication is not possible, the client this is
being developed for is insisting on not using it so I don't have much
choice.

The problem I have is that the example I gave is a basic version. I
have 1 central server and 6 local servers so giving odd/even isn't
possible. All the local servers are replicating their changes to the
central server and it's in turn replicating back down to the local
server which sent the data. The problem that I described above is
still essentially the problem I need to solve.

I thought there would of been a very easy way to turn of triggers but
this seems not the case?

Cheers,
Steve.

Hi Steve,

It sounds like your client needs a talking to! There is a technological
solution (replication) which has been extensively developed and tested,
and yet they want you to produce something which (not trying to be
nasty) won't be as thoroughly tested and validated?

I know that sometimes people come back with statements such as "I've
already said I can't do X", but can you tell us the reasons why the
clients have ruled out replication? Sometimes this helps in being able
to suggest alternatives (e.g. there may be alternate solutions which
would encounter exactly the same objections, so knowing the objections
helps us to frame suggestions that might be acceptable).

Damien

[Hugo Kornelis]

On 19 Oct 2006 10:02:06 -0700, st****@bluemania.co.uk wrote:

>Does anyone know how to do the following. I'm trying to mimic
replication with triggers.

On 19 Oct 2006 10:54:16 -0700, samh wrote:

Replication is not possible, the client this is
being developed for is insisting on not using it so I don't have much
choice.

Hi Steve,

Not an answer to the question, but solid advice nonetheless: make sure
to ask the client for his/her/their motives. As a consultant, you are
supposed to take the client serious, but you are also supposed to know
more about your subject than they do. They may have very valid motives,
but they also might be making their decision on urban legend, or on a
bug that has long been fixed. Ask them why replication is out of the
question, than decide whether to agree with them and roll your own, or
to explain to them why they should reconsider.

(snip)

>What I was hoping for was someway of saying, "I'm inserting from my
application so don't do the trigger".

Many years ago, we faced a similar issue when upgrading from SQL Server
4.2 (if memory serves) to 6.5. We used @@PROCID in the triggers to check
if the DML statement was executed in a stored procedure, and if so in
which sp. With 6.5, behaviour of @@PROCID changed - instead of the id of
the stored procedure that held the INSERT, UPDATE or DELETE statement,
it now held the id of the trigger itself. When we filed this as a bug,
we were brushed of by Microsoft. They claimed that the behaviour was as
intended, and when we pointed them to the documentation (that still
described the 4.2 behaviour), they claimed that the docs were wrong.

Since the workarounds supplied by MS didn't work either, we eventually
came up with our own kludge - we added a dummy column to the tables; in
stored procs that were supposed not to fire the trigger, we set the
value of this column to a dummy value. And in the FOR UPDATE trigger, we
included
IF UPDATE(DummyColumn) RETURN

Of course, this was only safe because all code was under our control. If
an end user would have had the ability to issue an UPDATE with the dummy
coolumn included in the SET clause, disaster would have followed. Also,
we were lucky only to need this in UPDATE triggers.

--
Hugo Kornelis, SQL Server MVP

[Malc]

st****@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

[David Portas]

st****@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.

You could put an INSTEAD OF trigger on a view over the target table.
Updates direct to the table or via another view won't be affected.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[samh]

Thank you all for your reply. Malc, your suggestion is just what I was
looking for, thank you.

I would love to use replication but the client we are developing this
for just doesn't want it. The problem is they have another system
(written by another company) that uses replication and they have
serious concerns about the amount of data being sent around their
network. Now this seemed strange to me and I identified that the data
was being replicated from all all machines to ALL machines! The client
(IT manager in particular!) still insists that replication isn't a good
solution and all I can do is write something similar but on smaller
scale.

Having written something the problem that I've identified now is the
speed. Running 1000 insert statements one after another is just way to
slow. Especially considering its a server sending these to another
server down a v. slow line. If anyone can suggest a quicker way of
moving the data I'd ve grateful. I thought maybe about a linked server
on each local server but I thought i'd see if anyone else has any
suggestions. There's BCP but I'm not sure this is relevant???

Steve.

[samh]

On a side note...

I am working on a replication based approach at the same time to
hopefully show it does work when configured correctly!