-1

There is one specific column whose value automatically changes to null after data is inserted into it.

I have tried to update the column from designer, update the column with query, delete the previous record and insert a new record but in all situations the column value goes back to null.

The issue is happening with the stock column of the table, I have tried updating the value of other columns in the table and it updates without any problem.

There are no triggers in the table either, what could be causing this issue?

Create table script below:

USE [AlliedGearShop - Production]
GO
/****** Object:  Table [dbo].[ProductVariants]    Script Date: 12/09/2019 02:23:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ProductVariants](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ProductID] [int] NOT NULL,
    [Weight] [decimal](18, 2) NULL,
    [Size] [varchar](250) NULL,
    [Color] [varchar](50) NULL,
    [Cost] [decimal](18, 2) NULL,
    [Gender] [varchar](50) NULL,
    [Attribute1] [varchar](50) NULL,
    [Attribute2] [varchar](50) NULL,
    [Attribute3] [varchar](50) NULL,
    [GPNumber] [nvarchar](50) NULL,
    [Stock] [int] NULL,
    [ImagePath] [nvarchar](150) NULL,
 CONSTRAINT [PK_ProductVariants] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  ForeignKey [FK_ProductVariants_Products]    Script Date: 12/09/2019 02:23:18 ******/
ALTER TABLE [dbo].[ProductVariants]  WITH CHECK ADD  CONSTRAINT [FK_ProductVariants_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ID])
GO
ALTER TABLE [dbo].[ProductVariants] CHECK CONSTRAINT [FK_ProductVariants_Products]
GO
0
2

There are no reasons beyond

1) some session is updating the table,

2) it has a trigger,

3) there's a database corruption,

4) a SQL Server bug.

I would attach Profiler or run an Extended Events trace capturing all statements, while performing the update to rule out 1) and 2), and run dbcc checkdb to eliminate 3).

1
  • 1
    There is an sql job getting executed after 1 minute which was causing the column to update. The profiler helped me figure it out. Dec 9 '19 at 18:01

Not the answer you're looking for? Browse other questions tagged or ask your own question.