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

Question

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....

Answer 1

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.