Passing a value to a non existing column in a trigger

Hi Peeps,

I have csv files that will be imported using dts. The file will contain 4 columns. “C1”, “C2”, “C3”, “C4”. This will be imported into a temporary table, “TBL_TEMP” with columns “C1”, “C2”, “C3”, “C4”. After I have validated the data it will be inserted into “TBL_REAL” with columns “KEY1”, “C1”, “C2”, “C3”. Now, column “KEY1” should be generated based on the value of column “C4” in “TBL_TEMP”. Another table “TBL_IDENT” with columns Col1, Col2 is used to bet the next numeric value for a specific C4 type.

So.. “TBL_IDENT” will have two records
Record 1: Col1 = “Val1”, Col2 = 1
Record 2: Col1 = “Val2”, Col2 = 101

“KEY1” – SELECT C4 + CONVERT(RIGHT(‘0000’ + VARCHAR(100), (SELECT Col2 FROM TBL_IDENT WHERE Col1 = C4)))

“KEY1” should be generated when a record is inserted into the “TBL_REAL” table and the “C4” value is “Val1” or “Val2”. “TBL_REAL” doesn’t except column “C4” so the value cannot be read from the inserted table using a instead of insert trigger on the “TBL_REAL” table. I have thought of using a view and adding a trigger on the view but records cannot be passed to a derived or constant field. I do not really want to create a duplicate table of “TBL_REAL” with the addition of a “C4” column nor do I want to add the “C4” column to the “TBL_REAL” table as it will never be used except for the generation of “KEY1”.

Do you guys have any suggestions?