20th November 2013
Several years ago I had written a blog on checkout tool that written in SQL CLR. The solution was based on a sql table trigger on the object table and would write the data into a Checkout table. If another user tried to modify the same object, the user would get an error that somebody else had modified the object. In NAV 2009 Microsoft introduced the checkout functionality. They added a new field in the object table called Locked and Locked by and In Tools->Option Microsoft added the property “Auto-Lock on Design”. This option isn’t usefully since a lot of times a developer just designs objects for research purposes and doesn’t want to lock the object. A lot of NAV development involves finding where the code is and how it works. The original tool I had written still works in NAV 2013. The issue is that in order to check in an object you have to run the table and delete the record. As you know data manipulation is done from RTC only in NAV 2013. I’ve built a new solution that uses the new fields: Locked, Locked By, in the Object table. The solution does not use any new objects. It’ s just a SQL trigger on Object Tracking table. When an object is modified in NAV, the Development Client write/updates the Object Tracking table. The sql trigger then updates the object table Locked and Locked By fields.
Here is the script.
CREATE TRIGGER [AutoLock] ON [dbo].[Object Tracking]
AFTER INSERT, UPDATE
DECLARE @Type int
DECLARE @ObjectID int
DECLARE @ChangeType int
DECLARE @LockedBY VarChar(132)
Set @Type = (select [Object Type] from inserted )
Set @ObjectID = (select [Object ID]from inserted )
Set @ChangeType = (select [Change Type] from inserted )
Set @LockedBY = (select [Locked By] from [Object] where Type=@Type and [ID] = @ObjectID)
if (@Type > 0) and (@ChangeType =0) and (@LockedBy = '') begin
set [Locked By] = SYSTEM_USER,
[Locked] = 1
where Type=@Type and [ID] = @ObjectID
This trigger only fires when the developer modifies/insert an object. This way you can design an object without worrying that it would lock the object. To release the Lock, the developer simply select one or more objects and selects Function->releases locks. If you are deleting objects, make sure you Lock them first. This solution can work with 2009 R2 as well. Enjoy.