我有一个名为MACHINES的Sql表，只有很少的行和列，例如MachineId，MachineName，ProgramName，RightCount和FinishingTime。 这些值从文本文件（xyz.txt）中读取并插入到此表中。 每次在该文本文件中读取新值时更新该行。 我的问题是我怎样才能使一个额外的表具有此MACHINES表的历史。 因此，如果我的RightCount值在文本文件中从3更改为4，我可以跟踪它。在'MACHINES'表中的行得到更新，但保留了MachineId 4中某个示例的历史记录。 每当我的文本文件数据发生变化时，我的总目标就是不让Table'MACHINES'拥有如此多的数据。 它需要我的数据库空间。 这与外键有关吗？ 此过程的SQL脚本将非常有用。 非常感谢。
我试过 - >但是当它更新时，我的历史表中没有数据。
CREATE TABLE try.dbo.MACHINES (MachineId bigint IDENTITY(1,1) PRIMARY KEY, MachineName varchar(50) NOT NULL, ProgramName varchar(255) NOT NULL, RightCount int, FinishingTime varchar(255) NULL, ); CREATE TABLE try.dbo.MACHINES_HISTORY (MachineStatusHistoryId bigint IDENTITY(1,1) PRIMARY KEY, MachineId bigint NOT NULL, CONSTRAINT FK_StatusHistory_Machines FOREIGN KEY (MachineId) REFERENCES dbo.MACHINES (MachineId), );I have a Sql table called MACHINES having few rows and columns, for example MachineId, MachineName, ProgramName, RightCount and FinishingTime. The values are read from a text file (xyz.txt) and inserted into this table. Every time the row gets updated when new values are read in this text file. My question is how can i make an extra table having a history of this MACHINES table. So that i can keep a track of it, in case if my RightCount value in textfile is changed from 3 to 4. The row in the 'MACHINES' table gets updated, but keeps a history for an example in MachineId 4 something changed. My overall goal is not to have Table 'MACHINES' with so much data when everytime my text file data gets changed. It takes my database space. Is it something to do with a foreign key. A SQL script for this procedure would be very helpful. Thank you so much.
I tried -> but was not able to have data in my history table when it got updated.
CREATE TABLE try.dbo.MACHINES (MachineId bigint IDENTITY(1,1) PRIMARY KEY, MachineName varchar(50) NOT NULL, ProgramName varchar(255) NOT NULL, RightCount int, FinishingTime varchar(255) NULL, ); CREATE TABLE try.dbo.MACHINES_HISTORY (MachineStatusHistoryId bigint IDENTITY(1,1) PRIMARY KEY, MachineId bigint NOT NULL, CONSTRAINT FK_StatusHistory_Machines FOREIGN KEY (MachineId) REFERENCES dbo.MACHINES (MachineId), );最满意答案
这是一个猜测，但是，下面的设置会创建一个从旧行插入到表Machine_History ：
CREATE TABLE dbo.Machine (MachineId bigint IDENTITY(1,1) PRIMARY KEY, MachineName varchar(50) NOT NULL, ProgramName varchar(255) NOT NULL, RightCount int, FinishingTime varchar(255) NULL--, --note sure why ther ewas a comma here? ); GO CREATE TABLE dbo.Machine_History (HistoryID bigint IDENTITY(1,1) PRIMARY KEY, MachineId bigint, MachineName varchar(50) NOT NULL, ProgramName varchar(255) NOT NULL, RightCount int, FinishingTime varchar(255) NULL, HistoryDate datetime2(0) DEFAULT GETDATE(), CONSTRAINT FK_MachineID FOREIGN KEY (MachineID) REFERENCES dbo.Machine (MachineID)); GO CREATE TRIGGER dbo.Machine_Update ON dbo.Machine AFTER UPDATE AS INSERT INTO Machine_History (MachineID, MachineName, ProgramName, RightCount, FinishingTime) SELECT MachineID, MachineName, ProgramName, RightCount, FinishingTime FROM deleted; GOThis is a bit of guesswork, however, the follow set up would create a INSERT the values from the old row into the table Machine_History:
CREATE TABLE dbo.Machine (MachineId bigint IDENTITY(1,1) PRIMARY KEY, MachineName varchar(50) NOT NULL, ProgramName varchar(255) NOT NULL, RightCount int, FinishingTime varchar(255) NULL--, --note sure why ther ewas a comma here? ); GO CREATE TABLE dbo.Machine_History (HistoryID bigint IDENTITY(1,1) PRIMARY KEY, MachineId bigint, MachineName varchar(50) NOT NULL, ProgramName varchar(255) NOT NULL, RightCount int, FinishingTime varchar(255) NULL, HistoryDate datetime2(0) DEFAULT GETDATE(), CONSTRAINT FK_MachineID FOREIGN KEY (MachineID) REFERENCES dbo.Machine (MachineID)); GO CREATE TRIGGER dbo.Machine_Update ON dbo.Machine AFTER UPDATE AS INSERT INTO Machine_History (MachineID, MachineName, ProgramName, RightCount, FinishingTime) SELECT MachineID, MachineName, ProgramName, RightCount, FinishingTime FROM deleted; GO更多推荐
发布评论