主キーが「ID,No」で、「ID」ごとに「No」を自動採番するトリガ。
＃NULL以外に任意の指定があればそちらを優先
┌──────────────────────────────────────┐
│CREATE TRIGGER [dbo].[トリガNo自動採番] ON [データベース].[dbo].[表]        │
│    INSTEAD OF  INSERT                                                      │
│AS                                                                          │
│    INSERT INTO [データベース].[dbo].[表]                                   │
│        (                                                                   │
│            [ID],                                                           │
│            [No],                                                           │
│            [列]                                                            │
│        )                                                                   │
│        SELECT [ID],                                                        │
│               CASE                                                         │
│                 WHEN [No] IS NULL THEN                                     │
│                     ISNULL(                                                │
│                         (SELECT MAX([別表].[No])                           │
│                              FROM [データベース].[dbo].[表] AS [別表]      │
│                              WHERE [別表].[ID] = inserted.[ID]),           │
│                         0                                                  │
│                     ) + ROW_NUMBER() OVER(ORDER BY [列] ASC)               │
│                 ELSE                                                       │
│                     [No]                                                   │
│               END,                                                         │
│               [列]                                                         │
│            FROM inserted;                                                  │
└──────────────────────────────────────┘
INSERTされる行が一行のみであれば「ROW_NUMBER() OVER(ORDER BY [列] ASC)」は
「1」で代用できる(SELECTによるINSERTの場合は複数行も想定される)

「ROW_NUMBER() OVER(ORDER BY [列] ASC)」で並び順に困った場合には、
「ROW_NUMBER() OVER(ORDER BY (SELECT 1))」って指定でもいいみたい。

但し、SELECTによるINSERTの場合はORDER BY句が効かないので
意図した追加順になるとは限らない。