การเพิ่มหมายเลขการแก้ไขในคีย์ผสมของตาราง

ฉันใช้ SQL Server 2014 ภายในเครื่องสำหรับฐานข้อมูลที่จะนำไปใช้กับฐานข้อมูล Azure SQL V12

ฉันมีตารางที่เก็บค่าของคุณสมบัติที่ขยายได้สำหรับออบเจ็กต์เอนทิตีธุรกิจ ในกรณีนี้ทั้งสามตารางจะมีลักษณะดังนี้:

CREATE TABLE Widgets (
    WidgetId bigint IDENTITY(1,1),
    ...
)

CREATE TABLE WidgetProperties (
    PropertyId int IDENTITY(1,1),
    Name       nvarchar(50)
    Type       int -- 0 = int, 1 = string, 2 = date, etc
)

CREATE TABLE WidgetPropertyValues (
    WidgetId   bigint,
    PropertyId int,
    Revision   int,
    DateTime   datetimeoffset(7),
    Value      varbinary(255)

    CONSTRAINT [PK_WidgetPropertyValues] PRIMARY KEY CLUSTERED (
        [WidgetId] ASC,
        [PropertyIdId] ASC,
        [Revision] ASC
    )
)

ALTER TABLE dbo.WidgetPropertyValues WITH CHECK ADD CONSTRAINT FK_WidgetPropertyValues_WidgetProperties FOREIGN KEY( PropertyId )
REFERENCES dbo.WidgetProperties ( PropertyId )

ALTER TABLE dbo.WidgetPropertyValues WITH CHECK ADD  CONSTRAINT FK_WidgetPropertyValues_Widgets FOREIGN KEY( WidgetId )
REFERENCES dbo.Widgets ( WidgetId )

ดังนั้นคุณจะเห็นว่า WidgetId, PropertyId, Revision เป็นคีย์ผสมอย่างไร และตารางจะจัดเก็บประวัติของค่าทั้งหมด (ค่าปัจจุบันได้มาจากการรับแถวที่มีหมายเลข Revision มากที่สุดสำหรับ WidgetId + PropertyId แต่ละตัว

ฉันต้องการทราบว่าฉันสามารถตั้งค่าคอลัมน์ Revision ให้เพิ่มขึ้นทีละ 1 สำหรับแต่ละ WidgetId + PropertyId ได้อย่างไร ฉันต้องการข้อมูลเช่นนี้:

WidgetId, PropertyId, Revision, DateTime, Value
------------------------------------------------
       1           1         1               123
       1           1         2               456
       1           1         3               789
       1           2         1               012

IDENTITY จะไม่ทำงานเนื่องจากเป็นแบบโกลบอลในตารางและเช่นเดียวกันกับวัตถุ SEQUENCE

อัปเดต ฉันสามารถคิดถึงวิธีแก้ปัญหาที่เป็นไปได้โดยใช้ทริกเกอร์ INSTEAD OF INSERT:

CREATE TRIGGER WidgetPropertyValueInsertTrigger ON WidgetPropertyValues
    INSTEAD OF INSERT
AS
BEGIN
    DECLARE @maxRevision int
    SELECT @maxRevision = ISNULL( MAX( Revision ), 0 ) FROM WidgetPropertyValues WHERE WidgetId = INSERTED.WidgetId AND PropertyId = INSERTED.PropertyId

    INSERT INTO WidgetPropertyValues VALUES (
        INSERTED.WidgetId,
        INSERTED.PropertyId,
        @maxRevision + 1,
        INSERTED.DateTime,
        INSERTED.Value,
    )
END

(สำหรับผู้ที่ไม่ได้ฝึกหัด ทริกเกอร์ INSTEAD OF INSERT จะทำงานแทนการดำเนินการ INSERT ใดๆ บนโต๊ะ เมื่อเปรียบเทียบกับทริกเกอร์ INSERT ปกติที่ทำงานก่อนหรือหลังการดำเนินการ INSERT)

ฉันคิดว่านี่จะปลอดภัยในการทำงานพร้อมกันเนื่องจากการดำเนินการ INSERT ทั้งหมดมีธุรกรรมโดยนัย และทริกเกอร์ที่เกี่ยวข้องใด ๆ จะถูกดำเนินการในบริบทธุรกรรมเดียวกัน ซึ่งหมายความว่าปลอดภัย เว้นแต่จะมีใครสามารถเรียกร้องเป็นอย่างอื่นได้?