Increate lock_version on changes

row_lock_trigger.sql

IF NOT EXISTS
(
	SELECT * FROM INFORMATION_SCHEMA.TABLES 
	WHERE TABLE_TYPE = 'BASE TABLE'
	AND TABLE_SCHEMA = 'dbo' 
	AND TABLE_NAME = 'room'
)
BEGIN
	EXECUTE(N'
CREATE TABLE dbo.room
(
	 rm_id int NOT NULL
	,rm_name national character varying(50) NULL
	,rm_nr int NULL
	,rm_lock_version int NULL CONSTRAINT df_room_rm_lock_version DEFAULT 0 
	,CONSTRAINT pk_room PRIMARY KEY ( rm_id ) 
); 
');
END
GO


IF NOT EXISTS 
(
	SELECT 1 
	FROM sys.triggers AS t
	JOIN sys.objects AS o ON o.object_id = t.parent_id 
	JOIN sys.schemas AS s ON s.schema_id = o.schema_id 
	WHERE t.name = 'trg_room_increment_lock_version' 
	AND o.name = 'room' 
	AND s.name = 'dbo' 
)
BEGIN
    EXECUTE('
CREATE TRIGGER trg_room_increment_lock_version 
	ON dbo.room
	AFTER UPDATE
AS
BEGIN
	PRINT ''BAM''; 
END
    ')
END


GO


ALTER TRIGGER trg_room_increment_lock_version
	ON dbo.room
	AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- CTE to identify rows where any relevant field changed (ignoring rm_lock_version)
    WITH ChangedRows AS 
	(
        SELECT 
			 deleted.rm_id 
			 -- (Latin1_General_CS_AS) — ensures 'Room A' ≠ 'room a'. 
			,deleted.rm_name COLLATE Latin1_General_CS_AS AS rm_name 
			,deleted.rm_nr 
        FROM deleted 
        EXCEPT 
        SELECT 
			 inserted.rm_id 
			 -- (Latin1_General_CS_AS) — ensures 'Room A' ≠ 'room a'. 
			,inserted.rm_name COLLATE Latin1_General_CS_AS AS rm_name 
			,inserted.rm_nr 
        FROM inserted 
    )
    -- Update only those rows where data changed (except lock_version)
    UPDATE r
		SET rm_lock_version = ISNULL(rm_lock_version, 0) + 1
    FROM dbo.room AS r 
    INNER JOIN ChangedRows AS c ON c.rm_id = r.rm_id; 
END;

GO



TRUNCATE TABLE dbo.room;
GO


DECLARE @new_id int;
-- Calculate the starting point for the new ID only ONCE before the loop
SET @new_id = ISNULL((SELECT MAX(rm_id) FROM dbo.room ), -1) + 1;


;WITH CTE AS
(
	SELECT @new_id AS i 
	UNION ALL 
	SELECT i+1 AS i FROM CTE 
	WHERE CTE.i < @new_id + 5 
) 
INSERT INTO dbo.room(rm_id, rm_name, rm_nr, rm_lock_version) 
SELECT 
     CTE.i AS rm_id
	,N'Test - ' + CAST((CTE.i + 1) AS national character varying(36)) AS rm_name
	,@new_id + 1 AS rm_nr
	,0 AS rm_lock_version
FROM CTE 
; 




SELECT 
	 rm_id 
	,rm_name 
	,rm_nr 
	,rm_lock_version 
FROM dbo.room 
; 

-- UPDATE room SET rm_name = 'Test ME'; 
-- UPDATE room SET rm_name = 'lol1', rm_nr = rm_nr -1; 
-- UPDATE room SET rm_lock_version = 0; 


-- OH CRAP: !!! 
SELECT 'room A'
EXCEPT 
SELECT ' Room A'
;

SELECT 'room A'
EXCEPT 
SELECT 'Room A'
;

-- Test case: 
UPDATE dbo.room SET rm_name = 'Room A' WHERE rm_id = 1; 
UPDATE dbo.room SET rm_name = 'Room a' WHERE rm_id = 1; 
SELECT * FROM dbo.room;