Automatical table update within a database

Hello!

We are developping a project using MS-SQLServer 7 and we need a
process for the synchronization of 3 tables together.
Inserts and updates in the table A should immediately and
automatically occur on table C, and updates on table C should also
automatically occur on table B.
We think that the solution using triggers and stored procedures is the
right choice. Could someone with knowledge on stored procedures help
us?
We need the help soon, this is quite urgent, so we’d be happy to
get an answer!
If something is not clear just ask!

Thanks in advance!
E. KellerLook at CREATE TRIGGER topic in the BOL

"E.Keller" <emmanuel.keller@.net2000.ch> wrote in message
news:4eed4cad.0401070114.65961769@.posting.google.c om...
> Hello!
> We are developping a project using MS-SQLServer 7 and we need a
> process for the synchronization of 3 tables together.
> Inserts and updates in the table A should immediately and
> automatically occur on table C, and updates on table C should also
> automatically occur on table B.
> We think that the solution using triggers and stored procedures is the
> right choice. Could someone with knowledge on stored procedures help
> us?
> We need the help soon, this is quite urgent, so we’d be happy to
> get an answer!
> If something is not clear just ask!
> Thanks in advance!
> E. Keller|||Use triggers if you cannot guarantee that the process that modifies table A
will also do the other two. If you do it all with stored procedures (my
preferred method) you can write the modification code for all three tables
inside the procs. make sure you wrap the 3 statements in a transaction so
they all complete or all roll back. Triggers automatically do the
transaction for you, but this is not always a good thing.

"E.Keller" <emmanuel.keller@.net2000.ch> wrote in message
news:4eed4cad.0401070114.65961769@.posting.google.c om...
> Hello!
> We are developping a project using MS-SQLServer 7 and we need a
> process for the synchronization of 3 tables together.
> Inserts and updates in the table A should immediately and
> automatically occur on table C, and updates on table C should also
> automatically occur on table B.
> We think that the solution using triggers and stored procedures is the
> right choice. Could someone with knowledge on stored procedures help
> us?
> We need the help soon, this is quite urgent, so we’d be happy to
> get an answer!
> If something is not clear just ask!
> Thanks in advance!
> E. Keller

Automatic synchronization of lookup columns

Data Flow:

Step 1: I use a OleDb Source Task to read data from a table (say table A) with 5 columns.

Step 2: Then I use a lookup task to add a sixth column (resulting from lookup on table B).

Step 3: Then I save the 6 columns to table C using a OleDb Destination Task.

Now, I go to underlying database and modify the tables like:

- remove 1 column from table A (not the column used for lookup later).
- remove 1 column from table B (not the columns used for lookup and not the columns retrieved after lookup)
- remove 1 column from table C (same as the one removed from table A).

If I get back to SSIS designer, it is nice enough to show warnings and ask whether I want to fix the metadata mismatch automatically. But, it does this for OleDb Source and Destination tasks only and NOT for lookup task. If I open the lookup task and hit the columns tab, it still shows old coulmn set for table B. It will be nice if these can be fixed automatically too. Or is it too much to ask for?

thanks,
Nitesh

Yes it'd be nice :)
You will notice similar behaviors also in other trasformations in the data flow (like Union All eg.). If you modify the structure of your data sources, you do need to check almost all of the sources, tasks and destinations involved.
Some months ago i asked in the beta place for a "Synchronize All" botton in the Data Flow editor form. But perhaps there will be better solutions then mine.
|||

Can you open a DCR for this?

thanks