Track and store sql table changes and display in datagridview

Question

Dear all,

I am very new to winforms & MSSQL ,
In my application i have table created in sql server and the same will entered and updated through my winform application.
Now i need to track and save all the changes happening to my sql table with new and old values.
My table looks like below:

When i changed to this:

My another table should have the updated value like this:

Changed by is System user name(it's okay if this can't be done)
Please suggest and share if you article or code which can solve this problem.
Thank you for the help in advance.
@Karen Payne MVP : Please help me.
Till now i have come up with below code , but i am having difficulties in calling old values.
Code:

create trigger ToolHistory_Insert on [Tool Management]  
after Insert  
as  
begin  
set nocount on;  
declare @oldID int  
declare @type nvarchar(20)  
declare @material float  
declare @matdes nvarchar(MAX)  
declare @toolno nvarchar(20)  
declare @tooldes nvarchar(MAX)  
declare @toollife float  
select @oldID = inserted.SL_NO , @type = inserted.[Type]  , @material = inserted.Material , @matdes = inserted.Material_Description , @toolno = inserted.Tool_Code  , @tooldes = inserted.Tool_Description  
 , @toollife = inserted.Tool_Life  
from inserted  
insert into PINQC.dbo.Tool_Management_History(Old_Table_ID , Old_Type , New_Type , Old_Material , New_Material , Old_Material_Description , New_Material_Description , Old_Tool_Code , New_Tool_Code ,   
Old_Tool_Description , New_Tool_Description , Old_Tool_Life , New_Tool_Life , Changed_By , Changed_At , Change_Type)  
values(@oldID  , [Tool Management].[Type] , @type , [Tool Management].Material , @material , [Tool Management].Material_Description , @matdes , [Tool Management].Tool_Code , @toolno ,   
[Tool Management].Tool_Description , @tooldes , [Tool Management].Tool_Life , @toollife , CURRENT_USER , GETDATE() , 'Inserted')  
END  

error message:

Msg 4104, Level 16, State 1, Procedure ToolHistory_Insert, Line 18 [Batch Start Line 0]  
The multi-part identifier "Tool Management.Type" could not be bound.  
Msg 4104, Level 16, State 1, Procedure ToolHistory_Insert, Line 18 [Batch Start Line 0]  
The multi-part identifier "Tool Management.Material" could not be bound.  
Msg 4104, Level 16, State 1, Procedure ToolHistory_Insert, Line 18 [Batch Start Line 0]  
The multi-part identifier "Tool Management.Material_Description" could not be bound.  
Msg 4104, Level 16, State 1, Procedure ToolHistory_Insert, Line 18 [Batch Start Line 0]  
The multi-part identifier "Tool Management.Tool_Code" could not be bound.  
Msg 4104, Level 16, State 1, Procedure ToolHistory_Insert, Line 19 [Batch Start Line 0]  
The multi-part identifier "Tool Management.Tool_Description" could not be bound.  
Msg 4104, Level 16, State 1, Procedure ToolHistory_Insert, Line 19 [Batch Start Line 0]  
The multi-part identifier "Tool Management.Tool_Life" could not be bound.  

Answer 1

Hi @Ramadas Ravikumar ,

Welcome to Microsoft Q&A!

Please refer to below and check whether it is helpful to you.

--DDL  
create table Tool_Management  
(ID int identity(1,1),  
Name varchar(100),  
Age int,  
Occupation varchar(100))  
  
insert into Tool_Management(Name,Age,Occupation) values  
('Ravi',26,'GoldSmith')  
  
create table Tool_Management_History  
(ID int identity(1,1),  
Master_Table_ID int,  
Old_Name varchar(100),  
New_Name varchar(100),  
Old_Age int,  
New_Age int,  
Old_Occupation varchar(100),  
New_Occupation varchar(100),  
Changed_By varchar(100),  
Changed_At datetime,  
Change_Type varchar(20))  
  
--create update trigger   
create trigger my_trigger_update_Tool_Management_History  
on Tool_Management  
after update  
as   
begin  
  insert into Tool_Management_History(Master_Table_ID,Old_Name ,New_Name ,Old_Age,New_Age,Old_Occupation,New_Occupation ,Changed_By,Changed_At,Change_Type)  
  select a.id,b.Name,case when a.Name=b.Name then NULL else a.Name end,b.Age,case when a.Age=b.Age then NULl else a.Age end  
  ,b.Occupation,case when a.Occupation=b.Occupation then NULL else a.Occupation end,SYSTEM_USER , GETDATE(),'updated'  
  from inserted a   
  inner join deleted b   
  on a.id=b.id  
end  
  
--create insert trigger   
create trigger my_trigger_insert_Tool_Management_History  
on Tool_Management  
after insert  
as   
begin  
  insert into Tool_Management_History(Master_Table_ID,Old_Name ,New_Name ,Old_Age,New_Age,Old_Occupation,New_Occupation ,Changed_By,Changed_At,Change_Type)  
  select id,NULL,Name,NULL,Age,NULL,Occupation,SYSTEM_USER , GETDATE(),'inserted'  
  from inserted   
end  
  
--perform some insert and update actions  
update  Tool_Management  
set Age=28  
where id=1  
  
insert into Tool_Management(Name,Age,Occupation) values  
('Sun',25,'Mary')  
  
--validate the results  
select * from Tool_Management  
select * from Tool_Management_History  

Output:

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

The statement multi-part identifier could not be bound in regards to Tool Management.Type for instance indicates the table cannot be found.

Did you write these statements in SSMS, ran them to verify they work? If not do that first. Also, better to have database, table and column names void of spaces. For instance [Tool Management] would change to ToolManagement.

Next up, seems prudent to not store age but instead calculate age client side (since you mentioned a DataGridView) using like found in this class and wrapped in this extension method. If using a DataTable age can be stored and formatted in the DataGridView designer.