DECLARE @MyTableVar TABLE
(
    empcode nvarchar(300)
);

BEGIN TRANSACTION

BEGIN TRY

	-- Update master table
	INSERT INTO EmployeeMaster.....
	OUTPUT INSERTED.empcode INTO @MyTableVar
	
	-- Update child table
	INSERT INTO EmployeeDetail (empcode, other fields...) 
	SELECT empcode
	FROM @MyTableVar
		INNER JOIN .... other tables as required

	-- Commit the transaction if successful
	COMMIT

END TRY

BEGIN CATCH

	-- Rollback transaction if unsuccessful
	ROLLBACK

END CATCH