Reacting to DB events

I'd like to build an application that will react to specific changes to
data in a set of tables in a database. The application would replicate
these data changes to another database. The target database won't be
SQLServer. Neither is this simple replication, at times the application
will need to get extra data from the source database before the target
is updated.
In other DBMS systems I am involved with the DBMS has the facilty to
write to an application message queue so that the monitoring application
only has to monitor the queue rather than a database. What I'd like to
do is something like this:
1. Some application changes data in a table.
2. A trigger reacts to the change and writes a message to an application
queue.
3. A windows service/process monitors the queue and picks up the
message. It then carries out whatever replication/DB actions are necessary.
This would mean defining a number of new triggers on existing tables and
developing the windows service/process. Existing applications and the
existing tables in the database would remain unchanged. I'm not a
windows programmer but I have someone in my team who is and who will
build the windows service/process.
The bit I'm unsure about is how a trigger can write to an application
queue or communicate with the windows service/process. I may be using
the wrong terminology as I have more knowledge of Unix than Windows.
Could anyone help with how I can do this or suggest any alternative
strategies.
Thanks In Advance.
LaurenceYou can get all the queuing infrastructure for free. SQL Server 2005 include
s a functionality called
"Service Broker", where your trigger does SEND to write to the queue and the
service uses RECEIVE to
read off of the queue.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Laurence Breeze" <i.l.breeze@.open.ac.uk> wrote in message news:43A2EAFC.5020500@.open.ac.uk
..
> I'd like to build an application that will react to specific changes to da
ta in a set of tables in
> a database. The application would replicate these data changes to another
database. The target
> database won't be SQLServer. Neither is this simple replication, at times
the application will
> need to get extra data from the source database before the target is updat
ed.
> In other DBMS systems I am involved with the DBMS has the facilty to write
to an application
> message queue so that the monitoring application only has to monitor the q
ueue rather than a
> database. What I'd like to do is something like this:
> 1. Some application changes data in a table.
> 2. A trigger reacts to the change and writes a message to an application q
ueue.
> 3. A windows service/process monitors the queue and picks up the message.
It then carries out
> whatever replication/DB actions are necessary.
> This would mean defining a number of new triggers on existing tables and d
eveloping the windows
> service/process. Existing applications and the existing tables in the dat
abase would remain
> unchanged. I'm not a windows programmer but I have someone in my team who
is and who will build
> the windows service/process.
> The bit I'm unsure about is how a trigger can write to an application queu
e or communicate with
> the windows service/process. I may be using the wrong terminology as I ha
ve more knowledge of
> Unix than Windows.
> Could anyone help with how I can do this or suggest any alternative strate
gies.
> Thanks In Advance.
> Laurence
>|||Thanks Tibor,
We currently run SQL Server 2000 but will upgrade to 2005 in time. If
there's no way to do this in 2000 I'll have to wait a bit.
Thanks Again.
Laurence
Tibor Karaszi wrote:
> You can get all the queuing infrastructure for free. SQL Server 2005
> includes a functionality called "Service Broker", where your trigger
> does SEND to write to the queue and the service uses RECEIVE to read off
> of the queue.
>|||There is nothing that stop you from doing this now. Just have the trigger wr
ite to a table and have
the app polling the table, reading off of it and removing the rows that has
been handled. But, I
think you should look into Service Broker as it provide you with so much inf
rastructure code. At
least grab a book and read about SB (or play with it), so you can make the r
ight decision. I
wouldn't be surprised if you in the end decide to wait until 2005 is impleme
nted to cut down on the
dev time for this (thanks to Service Broker).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Laurence Breeze" <i.l.breeze@.open.ac.uk> wrote in message news:43A7CA1D.6060804@.open.ac.uk
..
> Thanks Tibor,
> We currently run SQL Server 2000 but will upgrade to 2005 in time. If the
re's no way to do this
> in 2000 I'll have to wait a bit.
> Thanks Again.
> Laurence
>
> Tibor Karaszi wrote:
>