0

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),
    );
user9630935
  • 349
  • 1
  • 4
  • 18
  • Use a trigger and `INSERT` the values from `deleted` into your audit/history table? Your history table confuses me a little, as it just seems be be an ID and a foreign ID; thus it doesn't seem to provide any history/auditability. Generally history/audit tables include all the details of prior entries, so that the changes can be seen. – Thom A Apr 27 '18 at 08:21
  • okie forget the history table. That was my approach. In general can i get the suggestion for how to insert the values into the same row. i mean updating the row and keeping the old values in a other table – user9630935 Apr 27 '18 at 08:41

1 Answers1

1

This 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
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Thank you. But i get an error CREATE/ALTER TRIGGER' does not allow specifying the database name as a prefix to the object name. and is it MachineID or MachineId ? – user9630935 Apr 27 '18 at 10:31
  • The error is telling you the problem here, you're doing `CREATE TRIGGER Try.dbo.Machine_Update...` instead of `USE Try; CREATE TRIGGER dbo.Machine_Update...`. And those were sample tables, you know what your column names are better than I; I don't have access to your Server. :) – Thom A Apr 27 '18 at 10:34
  • okie Larnu. i get something like Cannot create trigger on 'devLaserViso.dbo.Machine' as the target is not in the current database. I have the table in my database. as per your script it creates two table and if there is a change in the Machine table it writes the old value into Machine_history table isn't it ? – user9630935 Apr 27 '18 at 11:08
  • According to your sample script, your database is called `Try` not `devLaserViso`. You need to trouble shoot this part yourself. The SQL I have provided works if you run it all within the context of a single database; thus it seems the problem is you're doing something else. – Thom A Apr 27 '18 at 11:10
  • oh sorry. it was my mistake. it is 'try' database. Even if i use it as your suggestion. i am getting an error Cannot create trigger on try.dbo.Machine' as the target is not in the current database. I did not get the Machine_update. is it a different table ? – user9630935 Apr 27 '18 at 11:17
  • Are you doing `CREATE TRIGGER Try.dbo.Machine_Update...` or `CREATE TRIGGER dbo.Machine_Update...`? – Thom A Apr 27 '18 at 11:24
  • When i use try, i get - CREATE/ALTER TRIGGER' does not allow specifying the database name as a prefix to the object name. WiTout try i get -Cannot create trigger on 'try.dbo.Machine' as the target is not in the current database. – user9630935 Apr 27 '18 at 11:31
  • What is the collation of your database? Considering that the table is called `MACHINES` in your sample, and you're using `Machines`, in a case sensitive collation these are 2 different objects. otherwise it sounds likes your still connected to the wrong database. – Thom A Apr 27 '18 at 11:35
  • It worked. I used GO before create trigger, Thank you Larnu. really helped. – user9630935 Apr 27 '18 at 11:39
  • No larnu. all good. I had one for testing. The table name is Machine. Machines was for testing! Overall your script was right just with a missing databasename before creating trigger. Thank you. – user9630935 Apr 27 '18 at 11:44