The updated record inserts a non-old record into the history table
I have two tables Test and TestHistory
CREATE TABLE [dbo].[TEST](
[ID] [int] NULL,
[Name] [varchar](10) NULL,
[Status] [char](1) NULL,
[CreatedDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_History](
[ID] [int] NULL,
[Name] [varchar](10) NULL,
[Status] [char](1) NULL,
[CreatedDate] [datetime] NULL
) ON [PRIMARY]
GO
INSERT INTO TEST ([ID],[Name],[Status],[CreatedDate])values (1,'Mohan','A',GETDATE())
Trigger created:
ALTER TRIGGER [dbo].[trg_Test]
ON [dbo].[TEST]
FOR UPDATE
AS
Declare @ID INT;
Declare @Name varchar(10);
Declare @Status CHAR(2);
Declare @CreatedDate DATETIME;
Select @ID = I.ID from INSERTED I
Select @Name = I.Name from INSERTED I
Select @Status = I.Status from INSERTED I
Select @CreatedDate = I.CreatedDate from INSERTED I
INSERT INTO [dbo].[Test_history]
([ID]
,[Name]
,[Status]
,[CreatedDate]
)
SELECT @ID,
@Name,
@Status,
GETDATE()
FROM INSERTED I
WHERE @ID = [ID]
When I update a record like
Update [TEST] SET Status = 'I'
then the old record with Status = 'A'
should be inserted, but what I am updating was not inserting the old record into the Testhistory table where I am doing wrong and how to insert the old value
as if i was updating Status = 'I'
and Status = 'A'
shoul was added in history table
source to share
You need INSERT
from DELETED
not from INSERTED
.
See examples here Understanding Inserted and Deleted SQL Server Tables for DML Triggers .
source to share
As Karl mentioned, you need to reference the remote table for the old updated row information. Also, your existing code doesn't work if you update more than one line. You need something like this.
ALTER TRIGGER [dbo].[trg_Test]
ON [dbo].[TEST]
FOR UPDATE
AS
INSERT INTO [dbo].[Test_history]
(
[ID],
[Name],
[Status],
[CreatedDate]
)
SELECT ID,
Name,
Status,
GETDATE()
FROM deleted d
source to share