For example, I am having table T1 and archive table T1_A
Create table T1(n number); Create table T1_A(archieve_dt date,n number);
To make activation working correctly if I am adding a column to T1 I need to add same column to T1_A.
Is there any way to call?
Alter table T1_A add n1 number;
If I am calling this
Alter table T1 add n1 number;
Did not find anything about triggers like this .
Advertisement
Answer
Broadly speaking if you need to do this you have a broken data model. Just because one table gets a new column there is no reason some other table should automatically get the same new column.
Applying DDL to a table should be a matter of wise human judgement, and executed in a controlled fashion, ideally with scripts out of source control. Having the database spawn columns from triggers would be a regression nightmare (even if it were possible, which it might be). Most managers would have serious concerns regarding the lack of control over schema changes in Production.
Obviously you seem to have a special case here with archiving, but actually the same principle applies. Consider what should happen if you were to drop a column from T1? Would you also want to drop the column from the archive table? Maybe, maybe not. Similarly if the alteration is to modify the datatype. In both cases you need to make an architectural decision about how to apply the change to the archive table.
Then of course there’s the matter of how you’re going to populate the archive table with the new column. Presumably there’s a trigger. Well the trigger will have to change, and while it’s common to generate trigger bodies from the data dictionary it’s still not the sort of thing you’d want to have happen automatically.
If you have a burning need to add the same column to multiple tables just write multiple ALTER TABLE statements. If there are too many to write by hand – and you don’t consider that a red flag – then you can generate statements with a query which drives off the data dictionary.