March 6, 2003 at 11:26 am
We are trying to develop an independent automated datamart. The application has to pickup any changes in any objects similar to how replication works. But as a software we donot want to use replication to be part of it, instead we are trying to use triggers for it. Problem with the normal usage of trigger is that it has to be applied for all objects seperately. Instead what we want is to find out a central place where all transactions are recorded and to identify them from there. The question is how to implement the above idea. Any advice/suggestions?
March 6, 2003 at 12:10 pm
One method could be:
You can determine changes to the schema of your databases with the create date and version columns in the sysobjects tables, if you record the previous values to compare against.
Differential queries will get the data. (Based on the fact that you mention triggers, I'd say this would work fine for you, as triggers are not going to perform schema changes anyway.) This is also a tried and true method, used before we had replication.
An alternative would be based on a log reader to read the transactions, if you are capable of that....(got a REALLY good C programmer, and a SQL GOD?)
and I've seen systems which were being profiled on a continuous basis, and the profiled data stored for compare with the changes distributed to the end systems.
All of these have their issues (some of them are quite major), and depending on what your criteria for the replication is, may or may not be appropriate for you. Also, NONE of them work as well as replication itself. Why is it that you do not want to use the natural replication for your needs?
March 6, 2003 at 12:33 pm
Thanks Scorpion_66. The idea is to develop a installable version of an application that will create a datamart for any database. So instead of adding to the database setup on the base database, we want to bring this bit as part of the application.
As you rightly said we have no gurus on C/SQL. SO the idea of sysobjects seems quite good. We also want to record and reflect any schema changes, which we can trace from sysobjects anyways.
The problem with the sysobjects will be to find out what data changes have occured in a particular object. That is we want to determine the actual row that is inserted/deleted/updated within a particular table. The issue spirals. Let me have a look. Any more suggestions are appreciated (not being cheeky).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply