SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[Service]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ServiceName] varchar(50) NOT NULL,

    CONSTRAINT [PK_Service] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]


CREATE TABLE [dbo].[ServiceRate]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ServiceId] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [CountryId] [int] NOT NULL,

    CONSTRAINT [PK_ServiceRate] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]


ALTER TABLE [dbo].[ServiceRate] WITH CHECK 
    ADD CONSTRAINT [FK_ServiceRate_Service] 
        FOREIGN KEY([ServiceId]) REFERENCES [dbo].[Service] ([Id])


ALTER TABLE [dbo].[ServiceRate] CHECK CONSTRAINT [FK_ServiceRate_Service]


ALTER TABLE [dbo].[ServiceRate] WITH CHECK 
    ADD CONSTRAINT [CK_ServiceRate_StartDate_EndDate] 
        CHECK  (([StartDate] <= COALESCE([EndDate], '9999-12-31')))

ALTER TABLE [dbo].[ServiceRate] CHECK CONSTRAINT [CK_ServiceRate_StartDate_EndDate]


CREATE UNIQUE NONCLUSTERED INDEX [UX_ServiceId_EndDate_CountryId] 
ON [dbo].[ServiceRate] ([ServiceId] ASC, [EndDate] ASC, [CountryId] ASC)
Insert Into Service Values(1),(2)

Insert into ServiceRate(serviceid, startdate, enddate, countryid)
Values
 (1, '2023-01-01', NULL, 0)
,(1, '2023/01/01', NULL, 53)
,(1, '2023/01/01', NULL, 70)
,(2, '2023/01/01', NULL, 0)
,(2, '2023/01/01', NULL, 53)


7 rows affected

Select * from ServiceRate


Id
ServiceId
StartDate
EndDate
CountryId


1
1
2023-01-01 00:00:00.000
null
0

2
1
2023-01-01 00:00:00.000
null
53

3
1
2023-01-01 00:00:00.000
null
70

4
2
2023-01-01 00:00:00.000
null
0

5
2
2023-01-01 00:00:00.000
null
53


Create Trigger CheckDateData ON ServiceRate 
INSTEAD OF Insert
AS
IF EXISTS (
Select Top 1 A.StartDate, A.EndDate, B.StartDate, B.EndDate 
  from ServiceRate A
  INNER JOIN Inserted B ON B.ServiceID = A.ServiceId AND B.CountryID = A.CountryID
   AND B.StartDate >= A.StartDate 
   AND ISNULL(B.EndDate,'9999-12-31') <= ISNULL(A.EndDate,'9999-12-31')
) 
RAISERROR ('Conflict in date insert',16,1)

INSERT INTO ServiceRate(serviceid, startdate, enddate, countryid)
Select b.serviceid, b.startdate, b.enddate, b.countryid
  from ServiceRate A
  RIGHT JOIN Inserted B ON B.ServiceID = A.ServiceId AND B.CountryID = A.CountryID
   AND B.StartDate >= A.StartDate 
   AND ISNULL(B.EndDate,'9999-12-31') <= ISNULL(A.EndDate,'9999-12-31')
  WHERE A.StartDate is null



Insert into ServiceRate(serviceid, startdate, enddate, countryid)
Values
 (1, '2023/01/01', '2023/02/28', 0)
Msg 50000 Level 16 State 1 Line 11
Conflict in date insert

select * from servicerate


Id
ServiceId
StartDate
EndDate
CountryId


1
1
2023-01-01 00:00:00.000
null
0

2
1
2023-01-01 00:00:00.000
null
53

3
1
2023-01-01 00:00:00.000
null
70

4
2
2023-01-01 00:00:00.000
null
0

5
2
2023-01-01 00:00:00.000
null
53
Id	ServiceId	StartDate	EndDate	CountryId
1	1	2023-01-01 00:00:00.000	null	0
2	1	2023-01-01 00:00:00.000	null	53
3	1	2023-01-01 00:00:00.000	null	70
4	2	2023-01-01 00:00:00.000	null	0
5	2	2023-01-01 00:00:00.000	null	53