Cacher is the code snippet organizer for pro developers

CREATE TRIGGER Trg_ControlNumber_Set_Code
ON  dbo.AppControlNumbers 
AFTER INSERT 
AS  
BEGIN 
	declare @prefix nvarchar(50)
	declare @id bigint
	declare @organizationUnitId bigint
	declare @newCode nvarchar(50)

	select @organizationUnitId = PartnerOrganizationUnitId, @id = Id from inserted
	select @prefix = Name from dbo.AppLookups as a where a.Category='OrgUnitCode' and a.Value = @organizationUnitId

	--Get New Control Number Code
	select @newCode = @prefix + cast(cast(replace(max(a.Code), @prefix, '') as int)+1 as nvarchar(50))  
	from dbo.AppControlNumbers as a 
	where a.PartnerOrganizationUnitId = @organizationUnitId

    SET NOCOUNT ON
	
    UPDATE dbo.AppControlNumbers 
    set Code = @newCode
    where Id = @id
END