CODEWITHSUNDEEP
sqlsql-servert-sql
Niroshan De Mel
Niroshan De Mel

Reputation: 35

Why columns value change into NULL when try to update only one column?

This is my table which I created in sql server...

CREATE TABLE [dbo].[Addresses_Table](
[AddressID] [int] IDENTITY(1,1) NOT NULL,
[BuildingName] [varchar](300) NULL,
[UnitNumber] [nvarchar](50) NULL,
[StreetNumber] [varchar](20) NULL,
[StreetName] [varchar](200) NULL,
[Suburb] [varchar](100) NULL,
[POBox] [varchar](20) NULL, 

   CONSTRAINT [PK_Addresses_Table] PRIMARY KEY CLUSTERED (
[AddressID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY] ) ON [PRIMARY]


INSERT INTO [dbo].[Addresses_Table] (BuildingName,UnitNumber,StreetNumber, StreetName, Suburb) VALUES ('mybuilding', '101', '12','Street 1', 'TEST12')

When I try to update 'POBox' column value into '1234' (any value), BuildingName, UnitNumber, StreetNumber, StreetName turn into NULL values. I have attached my sql update query and results of it.

Please help me out to solve this problem....

enter image description here

enter image description here

enter image description here

Upvotes: 0

Views: 888

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

It seems there is a trigger on the table that sets StreetName to NULL after the update. This is evidenced by the SSMS output that shows 2 rowcount messages, one when the row is updated directly and the other when the same row is updated by the trigger.

The trigger makes sense from a data perspective since StreetName does not apply to a PO box address.

Upvotes: 4

Related Questions