473,580 Members | 2,758 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How do you conditionally fire a trigger (mimic replication)

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.

Oct 19 '06 #1
9 2729
Steve,

If you absolutely do not want to use replication, and do not want to
add a column AddedOnServerNu mber, 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 AddedOnServerNu mber.

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

Oct 19 '06 #2
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.

Oct 19 '06 #3
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.
Oct 19 '06 #4
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

Oct 20 '06 #5
On 19 Oct 2006 10:02:06 -0700, st****@bluemani a.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(DummyCol umn) 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
Oct 22 '06 #6

st****@bluemani a.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 "replicatin g" 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\s pecial_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

Oct 23 '06 #7
st****@bluemani a.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
--

Oct 24 '06 #8
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.

Oct 25 '06 #9
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!

Oct 25 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4035
by: Chris W | last post by:
A friend of mine wants me to create a php web application that will show what tables or table/cols are being replicated and the current status for each replication. He is telling me it won't be using oracle specific coding. So I need to find a good website where I can read up on triggers and replication in oracle. Also recommendations for a...
6
3441
by: Dave C. | last post by:
Hello, I have created the following trigger: CREATE TRIGGER tr_UDFTest ON UserDefinedFields FOR INSERT, UPDATE AS DECLARE @foobar varchar(100) SELECT @foobar= foobar FROM inserted IF ( @foobar = 'foobar') INSERT INTO LogTable (LogText) values ('Found foobar')
0
1561
by: Marko Damaschke | last post by:
Hello alltogether, i'm working on a database-plattform which should work with fail-over-technics. Therefor 2 identic machines with internal RAID are available, which work in single-host-netloadbalance on win2003-server. Because of the internal RAID and caused by costs, a solution without external SCSI-RAID and win2003-clustering-mechanism is...
3
5731
by: dataguy | last post by:
I can't find it anywhere in the manual, but I have a developer that wants to know if we can code a trigger to capture the data that has changed in a table only if certain columns have changed. It looks like I can do it with the when clause and check the old vs new, but that would get very ugly with a large table. Has anyone done something...
8
2613
by: Frank van Vugt | last post by:
Hi, If during a transaction a number of deferred triggers are fired, what will be their execution order upon the commit? Will they be executed in order of firing or alfabetically or something entirely different? The docs only mention regular triggers being executed alfabetically.
2
10000
by: Dima Gofman | last post by:
I have a trigger on UPDATE on a table. I'm running some maintenance UPDATE and DELETE queries which I want the trigger to ignore but at the same time I want other UPDATE queries that other users might be running to keep triggering the trigger. Is there a SET statement perhaps that I could set before my query? Or a clause in the UPDATE...
8
7868
by: Benzine | last post by:
Hi, I have an issue with my replication at the moment. I will try to describe the scenario accurately. I am using MS SQL 2000 SP4 with Merge Replication. Subscribers connect to the publisher to upload/download changes. I have a trigger set up on one table which updates another, here is an example of the trigger: "CREATE TRIGGER...
2
2986
by: jcandersen | last post by:
Hi. I need to make a trigger which fires when after an UPDATE is done on table A in database X. The trigger must perform a statement which copies some of the data from the update to table B in database Y on the same server. My question is: Can I access the data which caused the trigger to fire or just get an indication of which entry in...
7
6370
by: srkidd12 | last post by:
Hello, Does anyone know how to get a trigger to fire while you are uploading multiple records into a SQL Server 2005 Express DB using the DTS Wizard? On insert of a person's record I want the trigger to find their id# from another table and update their information accordingly. I have the trigger built, but it will not fire when I use the...
0
7854
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8132
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8157
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6533
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5665
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5349
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3790
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3806
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
1118
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.