Track and store old and new value using trigger in MSSQL-CodePudding

I am very new to MSSQL , To track changes in sql table i have created a separate table and to populate the same when any changes happen i am writing this below trigger(currently i am writing for insert hence old and new value might seem silly but i need the same while anything gets updates)I am planning to write two more triggers for delete and update after this. Below is the trigger code but i can't get the old values , Please help me:

    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

here is the error message while executing trigger (i know i can't call values from [Tool Management] like this but i don't know other way)

    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.

CodePudding user response：

Your trigger makes a classic mistake: not taking into account multiple (or zero) rows in the inserted table.

Generally, you should combine INSERT UPDATE and DELETE triggers in one. But here it's a pretty simple trigger, so you can combine them by doing a full join on the inserted and deleted tables. You should join by primary key, I've assumed that is SL_NO

CREATE OR ALTER TRIGGER ToolHistory on [Tool Management]
AFTER INSERT, UPDATE, DELETE
AS

SET NOCOUNT ON;

INSERT 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)
SELECT
  ISNULL(i.SL_NO, d.SL_NO),
  d.[Type],
  i.[Type],
  d.Material,
  i.Material,
  d.Material_Description,
  i.Material_Description,
  d.Tool_Code,
  i.Tool_Code,
  d.Tool_Description,
  i.Tool_Description,
  d.Tool_Life,
  i.Tool_Life,
  CURRENT_USER,
  GETDATE(),
  CASE WHEN d.SL_NO IS NULL THEN 'Inserted' WHEN d.SL_NO IS NULL THEN 'Deleted' ELSE 'Update' END
FROM inserted i
FULL JOIN deleted d ON d.SL_NO = i.SL_NO;

GO