SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
    CREATE TABLE [dbo].[Rule]
    (
        [RuleId] [int] IDENTITY(1,1) NOT NULL,
        [Value1] [int] NOT NULL,
        [Value2] [int] NOT NULL,
        [Value3] [int] NOT NULL,
        AllTypeIds varchar(1600) NULL,

     CONSTRAINT [PK_Rule] PRIMARY KEY CLUSTERED ([RuleId] ASC),
     CONSTRAINT RuleMustBeUniqueValuesAndTypes UNIQUE (Value1, Value2, Value3, AllTypeIds)
     )
    
    CREATE TABLE [dbo].[Type]
    (
        [TypeId] [INT] IDENTITY(1,1) NOT NULL,
        [Description] [varchar](50) NOT NULL,
    CONSTRAINT [PK_Type] PRIMARY KEY CLUSTERED ([TypeId] ASC)
    )
    
     CREATE TABLE [dbo].[RuleType]
     (
        [RuleId] [int] NOT NULL,
        [TypeId] [int] NOT NULL,
     CONSTRAINT [PK_RuleType] PRIMARY KEY CLUSTERED ([RuleId] ASC,  [TypeId] ASC)
    )
    
    ALTER TABLE [dbo].[RuleType]  WITH CHECK ADD  CONSTRAINT [FK_RuleType_Type] FOREIGN KEY([TypeId])
    REFERENCES [dbo].[Type] ([TypeId])
    ALTER TABLE [dbo].[RuleType] CHECK CONSTRAINT [FK_RuleType_Type]
    ALTER TABLE [dbo].[RuleType]  WITH CHECK ADD  CONSTRAINT [FK_RuleType_Rule] FOREIGN KEY([RuleId])
    REFERENCES [dbo].[Rule] ([RuleId])
    ALTER TABLE [dbo].[RuleType] CHECK CONSTRAINT [FK_RuleType_Rule]

CREATE OR ALTER TRIGGER RuleType_UniqueRule
ON dbo.RuleType
AFTER INSERT, UPDATE, DELETE
AS

IF @@ROWCOUNT = 0
    RETURN;

SET NOCOUNT ON;

WITH ChangedRule AS (
    SELECT i.RuleId
    FROM inserted i
  
    UNION
  
    SELECT d.RuleId
    FROM deleted d
),
Aggregated AS (
    SELECT
      cr.RuleId, 
      STRING_AGG(CAST(rt.TypeId AS varchar(12)), '|')
        WITHIN GROUP (ORDER BY rt.TypeId) AS AllTypeIds
    FROM ChangedRule cr
    LEFT JOIN dbo.RuleType rt ON rt.RuleId = cr.RuleId
    GROUP BY
      cr.RuleId
)
UPDATE r
SET AllTypeIds = a.AllTypeIds
FROM dbo.[Rule] r WITH (SERIALIZABLE)
JOIN Aggregated a ON a.RuleId = r.RuleId
WHERE r.AllTypeIds IS DISTINCT FROM a.AllTypeIds;
/*Test data*/
INSERT INTO [dbo].[Type] ([Description]) VALUES ('Type1'), ('Type2'), ('Type3');
INSERT INTO [dbo].[Rule] ([Value1], [Value2], [Value3]) VALUES (1,1,1), (2,2,2), (3,3,3);
INSERT INTO [dbo].[RuleType] ([RuleId], [TypeId]) VALUES (1,1), (1,2), (1,3), (2,2), (2,3);
11 rows affected

SELECT * FROM [Rule];


RuleId
Value1
Value2
Value3
AllTypeIds


1
1
1
1
1|2|3

2
2
2
2
2|3

3
3
3
3
null


INSERT dbo.[Rule] ([Value1], [Value2], [Value3]) VALUES (1,1,1);
DECLARE @ruleId int = SCOPE_IDENTITY();

INSERT dbo.RuleType VALUES (@ruleId,1), (@ruleId,2);
3 rows affected

SELECT * FROM [Rule];


RuleId
Value1
Value2
Value3
AllTypeIds


4
1
1
1
1|2

1
1
1
1
1|2|3

2
2
2
2
2|3

3
3
3
3
null


INSERT dbo.[Rule] ([Value1], [Value2], [Value3]) VALUES (1,1,1);
DECLARE @ruleId int = SCOPE_IDENTITY();

INSERT dbo.RuleType VALUES (@ruleId,1), (@ruleId,2), (@ruleId,3);
Msg 2627 Level 14 State 1 Line 11
Violation of UNIQUE KEY constraint 'RuleMustBeUniqueValuesAndTypes'. Cannot insert duplicate key in object 'dbo.Rule'. The duplicate key value is (1, 1, 1, 1|2|3).
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.