Created
October 9, 2025 20:44
-
-
Save ststeiger/43bdd24c2da6de72ba88665336b05da8 to your computer and use it in GitHub Desktop.
Increate lock_version on changes
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment