January 13, 2012 at 6:52 am
Dear friends,
I need to:
copy TableA -> tableB (that will be done once of course)
inserts in TableA -> tableB
updates in TableA -> tableB
deletes in tableA -> NOTHIG WILL CHANGE IN tableB
imagine tableB as an archive table. and records that are achived would be deleted from live table, which is tableA.
ant suggestions?
January 13, 2012 at 7:01 am
Using an after insert, update trigger will probably be your best bet.
No need for a delete trigger as you're not passing on deletes 🙂
January 13, 2012 at 7:02 am
Your first copy is a simple insert statement
INSERT INTO table_b (<list of columns>)
SELECT <same list of columns>
FROM table_a
For your next step, it sounds like you need a couple triggers.
An INSERT and an UPDATE trigger should work.
You can put all your code inside the triggers to handle copying records that are being inserted or updated.
Or, an alternative way to do this would be with one trigger instead of two.
You would only need a delete trigger on table_a. When it fires, it copies the deleted record to table_b. This would be a little cleaner with less maintenance and a little less confusing in the future. table_b would be a history table containing items that no longer exist in table_a.
January 13, 2012 at 7:07 am
sorry, I needed to mention that I DO NOT KNOW THE FILED NAMES.
reason is I will run this query for every table. I need syntax like insert statement:
select * into tableB from tableA
every field and their vales should go its own place. Otherwise I need to extract every column name from schema which in that case I need to write so much code. control column types, etc.
January 13, 2012 at 7:31 am
Well you might be able to do that (select *) for inserts, if the column order is the same in both tables.
I don't think you can for updates though.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply