Assistance for duplicated tables required

[Timcwilcox]

I have a SQL server table that i am not allowed to alter (call it stock), so i want to maintain another table with the same schema initially, but this schema will change in time (call it NewStock).

The problem is i want to solve is when information is added, updated or removed from Stock, i want NewStock maintained with the same data.

The next problem is when NewStock is updated (it will never bee removed or added to), then Stock is automatically updated with the information.

I thought about doing this with a Trigger, but can i get the thing to work.

Am i going about this the right way, or is there a better solution.

Thanks in advance.

 

[osjohnm]

Hi

I reckon that triggers are your only option here.

Were asking if triggers would work or were you saying that you can't get the triggers to work?

You will need a an update trigger on new stock and insert, update and delete on old stock.

It might be better to create 3 triggers on stock to trap alll the scenarios and then use the inserted and delete virtual tables to join to new stock.

For the update trigger you might want to trap the updates per column so that you only update the actual column in new stock eg.

with your trigger code...
if update(stock_desc)
update new_stock set stock_desc = i.stock_desc
from inserted i, new_stock n
where i.stock_id = n.stock_id
if update(stock_date)
update new_stock set stock_date = i.stock_date
from inserted i, new_stock n
where i.stock_id = n.stock_id

Does that make sense?

If you want to you can post the structure of your tables and we can assist you with the syntax if would like us to help(if you run into difficulties or are unsure)

hope this helps

John

 

[MeanGreen]

Yes you will want to use a trigger for most of this:
Use a trigger for this work.
The problem is i want to solve is when information is added, updated or removed from Stock, i want NewStock maintained with the same data.

You cannot use a trigger here because you would be in a recursive loop. What you could do here is schedule an update to apply changes to your Stock table.
The next problem is when NewStock is updated (it will never bee removed or added to), then Stock is automatically updated with the information.


Hope this helps.