import data

We have a database in sql server. Now in the front end, we have two fields called latefee and latefeeDateReceived. When next time the people get another late fee. They just replace the old one and enter the new one. But now we want to keep all the history. Keep whatever they get the late fee. I am thing maybe need to create another table called latefee to contain these value. Is is good idea? As later they may want to get a report for all the late fee in one company. The thing is, I need to retrieve the current latefee and latefeeDateReceived and import to latefee table. But I need to create primary key for the latefee table. How can I import? Only one thing is I can import the data first, then add primary key. If I import later, how can I import. (Explain, the data retrieved have fields called CID, Latefee, latefeeDataReceived. But the table latefee has four fields called, lateID, CID, Latefee, latefeeDateReceived. When I import, it is always failed. Anybody can give me an idea? Many thanks.We have a database in sql server. Now in the front end, we have two fields called latefee and latefeeDateReceived. When next time the people get another late fee. They just replace the old one and enter the new one. But now we want to keep all the history. Keep whatever they get the late fee. I am thing maybe need to create another table called latefee to contain these value. Is is good idea? As later they may want to get a report for all the late fee in one company. The thing is, I need to retrieve the current latefee and latefeeDateReceived and import to latefee table. But I need to create primary key for the latefee table. How can I import? Only one thing is I can import the data first, then add primary key. If I import later, how can I import. (Explain, the data retrieved have fields called CID, Latefee, latefeeDataReceived. But the table latefee has four fields called, lateID, CID, Latefee, latefeeDateReceived. When I import, it is always failed. Anybody can give me an idea? Many thanks.

I think you want to create a trigger (or maybe a couple of triggers). I think you would wind up with something like:

CREATE Table DDL:

CREATE TABLE LateFee (
LateFeeID Int Identity (1,1) NOT NULL,
LateFee Decimal(10,2) NULL,
LateFeeReceived datetime NULL
)

CREATE TABLE LateFeeLog (
LateFeeLogID Int Identity (1,1) NOT NULL,
LateFeeID Int NOT NULL,
LateFee Decimal(10,2) NULL,
LateFeeReceived datetime NULL
)

Then you would need to create a couple of triggers:

-- First trigger for inserts
CREATE TRIGGER trgLateFeeInsert
ON LateFee
FOR INSERT

AS

INSERT INTO LateFeeLog (LateFeeID, LateFee)
SELECT LateFeeID, LateFee FROM inserted

-- second trigger for updates
CREATE TRIGGER trgLateFeeUpdate
ON LateFee
FOR UPDATE

AS UPDATE dbo.LateFeeLog
SET LateFeeReceived = inserted.LateFeeReceived
FROM dbo.LateFeeLog inner join inserted ON
dbo.LateFeeLog.LateFeeID = inserted.LateFeeID|||Thanks. This is big help.