How to ensure a field is populated before use in a trigger When I assign a new bed number in a hospital, the number must be prefixed by the ward number the bed is in. How do I know my user is going to populate Ward_Id before Bed_Id? What does one do in a case like this?

There is only one table:

CREATE TABLE [dbo].[Bed](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Bed_Id] [nvarchar](10) NOT NULL,
    [WardId] [int] NOT NULL,
    [Private] [bit] NOT NULL,
    [SpecialInfo] [nvarchar](max) NULL,
    [AdministratorId] [int] NOT NULL,
 CONSTRAINT [PK_Bed] 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] TEXTIMAGE_ON [PRIMARY]


When a bed gets added, Bed_Id must be prefixed by WardId.
Community wiki answer:

You could use a computed column for BedId. No need to be actually stored.

Would something like Bedid AS CONCAT(WardId, '-', Id) work for you?