Sometimes when I’m writing code, I come up with a solution that I’m particularly pleased with because it is just so neat. This is one of those times.
I’ve not been posting much on this blog recently because I’ve been very absorbed in a big project and been working crazy hours to get the coding done.
I should explain that this post is not about record locking to prevent concurrent update problems. This is about being able to put a more permanent lock on a record in order to stop updates being made to it until the lock is removed.
The project that I’ve been working on has some shared records in a few tables that are used by a large number of other records in other tables. The problem is that something was overwriting these shared records, which was pretty disasterous. I managed to resurrect the data, which wasn’t such a problem, but I was very worried about how to stop it happening again, since these records are accessed in a number of different places.
The obvious first step was to audit the code and check for what was doing it. I did that and identified some likely suspects and built in some code to prevent it happening again, but I wanted more reassurance than that, so I decided to try to get SQL Server itself to enforce a lock on them. The solution that I came up with was to use a trigger.
The code of the trigger is as follows
CREATE trigger [dbo].[trgEnforcePersonLock] ON [dbo].[Person]
for UPDATE
AS
BEGIN
if (select count(*) From deleted WHERE Locked=1)>0 AND (select count(*) From inserted WHERE Locked=1)>0
begin
rollback transaction
raiserror('Cannot modify a locked person!',16,1)
end
END
The inserted virtual table contains the new data and the deleted virtual table contains the old. I’m checking that the update isn’t unlocking it, because otherwise we’ll never be able to! It works by checking whether the record is locked and if so, rolling back the transaction and raising an error. From testing, it looks like it works pretty well!